quote:

---

Originally posted by rhart3

Hey,

I'm hoping someone can help me get rid of this headache I've had for the last couple days. I need to find the closest match to a variable in a select statement. The values being queried are numbers, not words, however I don't want the closest numeric match. I want the closest character match(es). If that doesn't make sense...

Let's say I have a column of data:

123
1234
12345
12364
32100

And I have a stored procedure that simply runs:

select * from table where column like '@variable%';

This works correctly if the variable entered is "1234" because the results that come back are 1234 and 12345. My problem is when the user enters the variable as "1234987"

My goal is to have the returned result in this case be 1234. Basically, I want to match the variable as far as possible starting from the first digit, and return whatever the closest match is that it can find.

Can anyone point me in the right direction?

Thanks in advance.

---

DECLARE @Val varchar(100)
SET @Val = '1234987'

SELECT TOP 1 Val
FROM
(

SELECT 123 AS Val UNION ALL
SELECT 1234 UNION ALL
SELECT 12345 UNION ALL
SELECT 12364 UNION ALL
SELECT 32100
)t
WHERE @Val LIKE '%' + CAST(Val as varchar(10)) + '%'
ORDER BY LEN(Val) DESC

output
-----------------------
Val
1234

SELECT TOP 1 Val
FROM yourtable
WHERE @Val LIKE '%' + CAST(Val as varchar(10)) + '%'
ORDER BY LEN(Val) DESC