扣除一个字符直到返回一行，并在返回值时修剪空格[Java/SQL]

Question

我需要使用Java在SQL中搜索字符串，期望是这样的:

输入:123456

SQL中要提取的数据:123777

result is:' AB C '

结果应为:'ABC'

迭代将像:

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 

这是我当前的代码:

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");
}
}
}

Answer 1

对于扣除字符，应该这样做:

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

对于trim空格，它应该是:

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