Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How could i get following result Expand / Collapse
Author
Message
Posted Tuesday, April 28, 2009 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 27, 2010 5:16 AM
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?
Post #705563
Posted Tuesday, April 28, 2009 2:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 6,957, Visits: 12,720
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
Post #705578
Posted Tuesday, April 28, 2009 3:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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

Post #705639
Posted Tuesday, April 28, 2009 4:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 6,957, Visits: 12,720
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
Post #705653
Posted Tuesday, April 28, 2009 9:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
Just a concept....

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

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #706439
Posted Wednesday, April 29, 2009 9:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
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!
Post #706972
Posted Wednesday, April 29, 2009 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 6,957, Visits: 12,720
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
Post #706990
Posted Wednesday, April 29, 2009 10:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
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!
Post #707000
Posted Wednesday, April 29, 2009 10:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 6,957, Visits: 12,720
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
Post #707014
Posted Wednesday, April 29, 2009 9:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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

Post #707373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse