Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How could i get following result Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 28, 2009 1:58 AM
 Forum 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 Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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 @tabSELECT 1, 95 UNION ALLSELECT 2, 959 UNION ALLSELECT 3, 44 UNION ALLSELECT 4, 959898 SELECT TOP 1 id, valFROM @tabWHERE PATINDEX('%'+CAST(val AS VARCHAR(20))+'%','9598989898954412') = 1ORDER BY val DESC-- Alternative code (the above is a little oversized for this purpose...) -> driven by Atif's solution below...SELECT TOP 1 id, valFROM @tabWHERE '9598989898954412' like CAST(val AS VARCHAR(20))+'%'ORDER BY val DESC-- result set:-- ID val-- 4 959898`Edit: added second option. LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks 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 Group: General Forum Members Last Login: Today @ 12:15 PM Points: 3,254, Visits: 5,153
 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 @t1where 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 Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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. LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks 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 Group: General Forum Members Last Login: Today @ 12:15 PM Points: 3,254, Visits: 5,153
 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 Group: General Forum Members Last Login: Friday, June 3, 2016 3:40 AM Points: 488, 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 @tabSELECT 1, 95 UNION ALLSELECT 2, 959 UNION ALLSELECT 3, 44 UNION ALLSELECT 4, 959898 SELECT TOP 1 id, valFROM @tabWHERE PATINDEX('%'+CAST(val AS VARCHAR(20))+'%','9598989898954412') = 1ORDER BY val DESC-- Alternative code (the above is a little oversized for this purpose...) -> driven by Atif's solution below...SELECT TOP 1 id, valFROM @tabWHERE '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 98954412in the input table, the code above will not be able to detect this because its patindex is > 1Your 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 @tSELECT 1, 95 UNION ALLSELECT 2, 959 UNION ALLSELECT 3, 44 UNION ALLSELECT 4, 959898 UNION ALLSELECT 5, 98954412 DROP TABLE #zSELECT id, num, LEN(num) matchlen, CHARINDEX(cast(num as varchar(20)),'9598989898954412',1) charposINTO #zFROM @tSELECT id, num FROM #z WHERE matchlen = (SELECT MAX(matchlen) FROM #z)` Saurabh Dwivedy___________________________________________________________My Blog: http://tinyurl.com/dwivedysFor better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537Be Happy!
Post #706972
 Posted Wednesday, April 29, 2009 9:55 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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 98954412in the input table, the code above will not be able to detect this because its patindex is > 1If 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... LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #706990
 Posted Wednesday, April 29, 2009 10:01 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 3, 2016 3:40 AM Points: 488, 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/dwivedysFor better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537Be Happy!
Post #707000
 Posted Wednesday, April 29, 2009 10:12 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 AFAIK it stand for Original Poster (the one who started the thread with the first post). LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks 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 Group: General Forum Members Last Login: Today @ 12:15 PM Points: 3,254, Visits: 5,153
 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

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.