CODEWITHSUNDEEP
phpmysqlsql
Sujan Biswas
Sujan Biswas

Reputation: 35

How to find the nearest string value from table?

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.

Upvotes: 1

Views: 93

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

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

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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.

Upvotes: 3

Related Questions