lmu92 (4/28/2009)
Hi,did you try the PATINDEX function?
The following code will check the start position of each value from your table within the given search string. It will return the max. number with start position = 1.
DECLARE @tab TABLE (id INT, val INT)
INSERT INTO @tab
SELECT 1, 95 UNION ALL
SELECT 2, 959 UNION ALL
SELECT 3, 44 UNION ALL
SELECT 4, 959898
SELECT TOP 1 id, val
FROM @tab
WHERE PATINDEX('%'+CAST(val AS VARCHAR(20))+'%','9598989898954412') = 1
ORDER BY val DESC
-- Alternative code (the above is a little oversized for this purpose...) -> driven by Atif's solution below...
SELECT TOP 1 id, val
FROM @tab
WHERE '9598989898954412' like CAST(val AS VARCHAR(20))+'%'
ORDER BY val DESC
-- result set:
-- ID val
-- 4 959898
Edit: added second option.
Just wanted to mention a small point here: Your code assumes that the starting position will always be 1. What happens if the match string were to be somewhere in the middle? For ex, if there was another number such as 98954412
in the input table, the code above will not be able to detect this because its patindex is > 1
Your solution is the most elegant solution otherwise. Patindex did not occur to me when I started solving this problem and ended up writing a long code.
DECLARE @t TABLE
(id INT, num INT)
INSERT into @t
SELECT 1, 95 UNION ALL
SELECT 2, 959 UNION ALL
SELECT 3, 44 UNION ALL
SELECT 4, 959898 UNION ALL
SELECT 5, 98954412
DROP TABLE #z
SELECT id, num, LEN(num) matchlen, CHARINDEX(cast(num as varchar(20)),'9598989898954412',1) charpos
INTO #z
FROM @t
SELECT id, num FROM #z
WHERE matchlen = (SELECT MAX(matchlen) FROM #z)
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537