Deduct one character until a row is returned, and trim spaces when returning values [Java/SQL]

Question

i need to search string in SQL using Java, expectation is something like this:

input: 123456

data in SQL to be fetched: 123777

result is: ' AB C '

result should be: 'ABC'

iteration will be like:

select col1, col2, col3 from table where input like '123456%'; --no row returned
select col1, col2, col3 from table where input like '12345%'; --no row returned
select col1, col2, col3 from table where input like '1234%'; --no row returned
select col1, col2, col3 from table where input like '123%'; --returns row for 123777

this is my current code:

public Output Method (String input) throws exception{

   Connection connection = getSQLConnection();
   String SQLquery = "SELECT COL1, COL2, COL3 FROM TABLE WHERE INPUT LIKE ?";

   if (connection != null){
      
      PreparedStatement ps = connection.prepareStatement(SQLquery);
      ps.setString(1, input + "%"); 
      ResultSet rs = ps.executeQuery();
      
      // how to deduct characters until a match is found?
      logger.debug("Executed: "+SQLquery+"; input => ["+input+"]"); 

      if(rs.next()){
         output = new Output();
         output.setOut1(rs.getString(1));
         output.setOut2(rs.getString(2));
         output.setOut2(rs.getString(3));
         //how to remove all spaces from in per result?
         //sample result: ' AB  C   ' -> should be 'ABC'

      }else{
         logger.debug("no row returned");
      }
   }
}

You can use `rs.getString(1).replaceAll("\\s", "")` to remove spaces. I don't really understand the problem of the other question. You just iterate over `input`, remove 1 character with each iteration till you get a result? — magicmn, Oct 21 '21 at 09:34
yes, correct. input is `123456` but available data is `123777`, it should loop by removing the last character all the time until it reaches a match in the table — dcdum2018, Oct 21 '21 at 09:45
You need to create a loop (probably using while) to make this happen. — Just another Java programmer, Oct 21 '21 at 09:52

score 0 · Answer 1 · answered Oct 28 '21 at 01:08

0

for deduct character, this should be done:

for(int i = 0; i < input.length(); i++){
   ps.setString(1, input.substring(0, input.length()-i)); 
}

for trim spaces, it should be:

output.setOut1(rs.getString(1).replaceAll("\\s", ""));

answered Oct 28 '21 at 01:08

dcdum2018

93
1
9

Deduct one character until a row is returned, and trim spaces when returning values [Java/SQL]

1 Answers1