How to find the nearest string value from table?

Question

I have a table name "test" having a column code (string format) and data is like:

U298765
U298799
U210430
U210499
B239856

Now I want to get data by input field entry. If a user write U298750, I want show the nearest value U298765 and for U210401,U210430.

Answer 1

You can use the right function to fetch the number and then use following logic.

Select t.*
  From test t
Order by abs(Right(code, length(code)-1) - Right(your_input, length(your_input)-1))
Limit 1

I am consodering that you need the nearest based on numbers only.

Answer 2

Try below query:

select  code from test
order by abs(abs(substring(code,2,length(code)))-abs(substring('U298750',2,length('U298750'))))
Limit 1

In place of 'U298750' use your input

Answer 3

You seem to just want:

select t.*
from t
where code >= ?   -- the input value
order by code desc
limit 1;

The ordering of strings alphabetically is sufficient for getting the "next" value after the string. There is no need to convert anything to numbers.