How could i get following result

  • 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?

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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
    [font="Arial Black"]here[/font][/url][/right]

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Just a concept....

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • 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!
  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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!
  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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
    [font="Arial Black"]here[/font][/url][/right]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply