none
Loop through string value t-sql

    Question

  • Hi Guys

    I have a string value that I need to mach against a table. 

    Value a:  abcded

    Value b: abc

    So I created a few IF statements to get it working but I want to use a loop instead and for life of me I just cant figure it out- Sorry I dont have the code.

    basically I need to remove a character until "Value a" is matched to "Value b" without using a like statement because some values can have abce.

    Thanks.

    Saturday, September 07, 2013 2:36 PM

Answers

  • If i understand correctly, i don't think we need to loop through each character for comparision. Why can't we use CHARINDEX or PATINDEX to identify the presence of a substring within a string ?


    Regards, RSingh

    Saturday, September 07, 2013 4:20 PM
  • Hi,

    Is this what you are trying :

    DECLARE @tmp1 TABLE (Valuea VARCHAR(20))
    INSERT @tmp1 SELECT 'abcded'
    INSERT @tmp1 SELECT 'ded'
    DECLARE @tmp2 TABLE (Valueb VARCHAR(20))
    INSERT @tmp2 SELECT 'abc'
    --approach 1
    SELECT * FROM @tmp1 A
    JOIN @tmp2 B 
    ON B.Valueb = SUBSTRING(A.Valuea,PATINDEX('%'+B.Valueb+'%',A.Valuea),LEN(B.Valueb))
    --approach 2
    SELECT * FROM @tmp1 A,@tmp2 B
    WHERE PATINDEX('%'+B.Valueb+'%',A.Valuea) > 0 


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, September 07, 2013 5:06 PM
    Moderator

All replies

  • Put the values into a table variable (@table) and build a cursor WHILE for the logic.

    T-SQL cursor example:

    http://www.sqlusa.com/bestpractices2005/doublecursor/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, September 07, 2013 2:42 PM
    Moderator

  • Value a:  abcded

    Value b: abc

    basically I need to remove a character until "Value a" is matched to "Value b" without using a like statement because some values can have abce.

    Try this code:

    DECLARE @T TABLE
        (
          Value NVARCHAR(200) ,
          Test NVARCHAR(200)
        )
    INSERT  @T
            ( Value, Test )
    VALUES  ( 'a', 'abcded' ),
            ( 'b', 'abc' )
    
    SELECT  *
    FROM    @T
    
    DECLARE @Value NVARCHAR(200) = 'abc'
    
    SELECT  *
    FROM    @T
    WHERE   Test = @Value
    


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Saturday, September 07, 2013 3:01 PM
  • If i understand correctly, i don't think we need to loop through each character for comparision. Why can't we use CHARINDEX or PATINDEX to identify the presence of a substring within a string ?


    Regards, RSingh

    Saturday, September 07, 2013 4:20 PM
  • Hi,

    Is this what you are trying :

    DECLARE @tmp1 TABLE (Valuea VARCHAR(20))
    INSERT @tmp1 SELECT 'abcded'
    INSERT @tmp1 SELECT 'ded'
    DECLARE @tmp2 TABLE (Valueb VARCHAR(20))
    INSERT @tmp2 SELECT 'abc'
    --approach 1
    SELECT * FROM @tmp1 A
    JOIN @tmp2 B 
    ON B.Valueb = SUBSTRING(A.Valuea,PATINDEX('%'+B.Valueb+'%',A.Valuea),LEN(B.Valueb))
    --approach 2
    SELECT * FROM @tmp1 A,@tmp2 B
    WHERE PATINDEX('%'+B.Valueb+'%',A.Valuea) > 0 


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, September 07, 2013 5:06 PM
    Moderator
  • Hi Guys

    thanks I will try a PATINDEX and see if I can use that.

    Regards

    • Proposed as answer by Papy Normand Sunday, September 08, 2013 7:32 PM
    • Unproposed as answer by Papy Normand Sunday, September 08, 2013 7:32 PM
    Sunday, September 08, 2013 8:29 AM
  • Hi Guys

    Thank you.  PATINDEX worked perfectly.

    Regards

    Sunday, September 08, 2013 6:32 PM