1

I am trying to use concat with a Derby database query. But i get syntax error. How can I use concat in Derby?

The below code is for search purpose

  public ArrayList <Tablearray>ListUsers(String ValToSearch){
  ArrayList <Tablearray> usersList  = new ArrayList <Tablearray>();
  Statement st;
  ResultSet rs;

  try {
        Connection con = getConnection();
        st = con.createStatement();
        String searchQuery = "SELECT * FROM ABYP WHERE CONCAT(Id_Search, Tyl, Apothkh, Parathrhseis,Ti) LIKE '%"+ValToSearch+"%'";
       //String searchQuery = "SELECT * FROM ABYP WHERE ||CONCAT|| (Id_Search) LIKE '%||"+ValToSearch+"||%'";
        //String searchQuery = "SELECT *FROM ABYP where ID_SEARCH =? ";
        rs = st.executeQuery(searchQuery);
  Tablearray tablearray;
  while (rs.next()){
  tablearray = new Tablearray (
    rs.getString("Id_Search"),
    rs.getString("Tyl"),
    rs.getString("Apothkh"),
    rs.getString("Parathrhseis"),
    rs.getString("Ti")
      );
  usersList.add(tablearray);
  }
  }catch(Exception ex){
        System.out.println(ex.getMessage());
    }

    return usersList;


  }
George Kontonikolaou
  • 109
  • 4
  • 15
  • 1
    Derby uses the SQL standard `||` concatenation operator: http://db.apache.org/derby/docs/10.12/ref/rrefsqlj40899.html –  Jan 11 '17 at 07:30
  • can i have an example please ??? with my query cause i can't do it i was trying with || but nothing ...tyy – George Kontonikolaou Jan 11 '17 at 07:32
  • 1
    `WHERE Id_Search||Tyl||Apothkh||Parathrhseis||Ti like ...` –  Jan 11 '17 at 07:33
  • Syntax error: Encountered "Id_Search" at line 1, column 33. "SELECT * FROM ABYP WHERE concat Id_Search||Tyl||Apothkh||Parathrhseis||Ti LIKE '%"+ValToSearch+"%'"; – George Kontonikolaou Jan 11 '17 at 07:36
  • String searchQuery = "SELECT * FROM ABYP WHERE concat (Id_Search||Tyl||Apothkh||Parathrhseis||Ti) LIKE '%"+ValToSearch+"%'"; that works but he tell me that Cannot convert types 'INTEGER' to 'VARCHAR'. so ?? how i can make it use for interger ? – George Kontonikolaou Jan 11 '17 at 07:39
  • 1
    Possible duplicate of [Derby: CONCAT equivalent](http://stackoverflow.com/questions/10203863/derby-concat-equivalent) – HoneyBadger Jan 11 '17 at 09:14
  • 1
    You may be able to use `CAST` on the integer to convert it to a string. – Bryan Pendleton Jan 11 '17 at 14:47

0 Answers0