SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How could i get following result


How could i get following result

Author
Message
reekdhanwani
reekdhanwani
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 54
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?
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10605 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3806 Visits: 5190
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



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10605 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3806 Visits: 5190
Just a concept....

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Saurabh Dwivedy
Saurabh Dwivedy
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 340
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

Be Happy!
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10605 Visits: 13559
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...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Saurabh Dwivedy
Saurabh Dwivedy
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 340
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

Be Happy!
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10605 Visits: 13559
AFAIK it stand for Original Poster (the one who started the thread with the first post).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3806 Visits: 5190
Both options are workable. I think OP would be a better person to decide if his/her requirement is met of not.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search