April 28, 2009 at 1:58 am
Suppose i have entered the no like 9598989898954412
and i have table which stores
id no
1 95
2 959
3 44
4 959898
so in this series i would like to catch the last no which is the same as input characters
although i have first ,second values for the match of input characters but i want to retrieve the longest match..
can anyone tell me how could i get it easily?
April 28, 2009 at 2:36 am
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.
April 28, 2009 at 3:48 am
Another Option...
Declare @t1 Table(mid int, val varchar(100))
Insert into @t1
Select 1,'123' union all
Select 2,'123456' union all
Select 3,'123456789' union all
Select 4,'123456789123'
Declare @vChk varchar(100)
Set @vChk = '123'
Select Top 1 * from @t1
where val like '%' + @vChk + '%'
Order By Len(val) desc
April 28, 2009 at 4:19 am
Hi Atif,
you need to remove the '%' from the left side of your like clause.
Otherwise a val='23456789123' will show up as a result, but it shouldn't.
April 28, 2009 at 9:28 pm
April 29, 2009 at 9:44 am
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
April 29, 2009 at 9:55 am
saurabh.dwivedy (4/29/2009)
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
If the match string can also be in the middle (which is not clear by the data / description provided by the OP), the PATINDEX function needs just a small modification:
Instead of checking for = 1 it needs to check for >0. This will include any matching values.
However, the data provided by the OP did indicate that a check for position 1 is required...
April 29, 2009 at 10:01 am
Point taken. That was just an after thought. That's the way I had read the requirement, even though as you mention, the OP had indeed not mentioned this as a requirement.
By the way... I am new to the forum and do not know what OP stands for 🙂 Just curious to know...
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 29, 2009 at 10:12 am
AFAIK it stand for Original Poster (the one who started the thread with the first post).
April 29, 2009 at 9:45 pm
Both options are workable. I think OP would be a better person to decide if his/her requirement is met of not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply