none
Using LIKE to Find Characters in a Specific Location in a String RRS feed

  • Question

  • How can I use wildcard characters to find a string within a string at a specific loction?  For example, if I have a column with these rows:

    C1L1abcdef

    C2L31234L1

    FGL1xyz1234567

    C1L2asdf

    ... and I want to select the first and third row by looking for "L1".  Notice that the second row has "L1" in it, but I don't want to select that row.  I have tried something like this:

    string myString = "C1"; string mySqlString = "SELECT * FROM myTable WHERE myTable.myColumn +

    LIKE '__" + myString + "%';"; // "__" contains 2 underscores.

    thinking that the search for "C1" should return no rows because "C1" is located in positions 1-2 in the string and I have stipulated (I thought) that I am looking for the "C1" in positions 3-4.  However, the first and fourth rows are returned. 


    REvans


    • Edited by REvans611 Wednesday, August 15, 2012 6:13 PM
    Wednesday, August 15, 2012 6:12 PM

Answers

  • Hi REvans,

    Your query should look like as follow in SSMS:

    SELECT * FROM myTable 
    WHERE myColumn LIKE '__' + 'C1' + '%'

    When executed with the sample data it returns no rows:

    If you are using this query in C#.NET code you need to write it as follow:

    "SELECT * FROM #myTable WHERE myColumn LIKE '__'" + myString + "'%'"


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, August 15, 2012 6:33 PM
    • Marked as answer by REvans611 Wednesday, August 15, 2012 6:48 PM
    Wednesday, August 15, 2012 6:32 PM

All replies

  • CREATE TABLE #t
    (id INT PRIMARY KEY IDENTITY(1,1)
    ,NAME VARCHAR(20)
    )
    INSERT INTO #t
    SELECT 'C1L1abcdef' UNION ALL 
    SELECT 'C2L31234L1' UNION ALL 
    SELECT 'FGL1xyz1234567' UNION ALL 
    SELECT 'C1L2asdf'
    SELECT * FROM #t;
    SELECT ID,NAME,SUBSTRING(NAME,CHARINDEX('L1',NAME),2 )
    FROM #t
    WHERE id%2<>0

    Wednesday, August 15, 2012 6:31 PM
  • Hi REvans,

    Your query should look like as follow in SSMS:

    SELECT * FROM myTable 
    WHERE myColumn LIKE '__' + 'C1' + '%'

    When executed with the sample data it returns no rows:

    If you are using this query in C#.NET code you need to write it as follow:

    "SELECT * FROM #myTable WHERE myColumn LIKE '__'" + myString + "'%'"


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, August 15, 2012 6:33 PM
    • Marked as answer by REvans611 Wednesday, August 15, 2012 6:48 PM
    Wednesday, August 15, 2012 6:32 PM
  • declare @test table(aString varchar(15));
    insert into @test
    select 'C1L1abcdef' union all
    select 'C2L31234L1' union all
    select 'FGL1xyz1234567' union all
    select 'C1L2asdf'
    ;
    select * 
    from @test
    join 
    ( select 'C1' as test union all select 'L1'
    ) as x
      on aString like '__' + test + '%';
    /* -------- Output: --------
    aString         test
    --------------- ----
    C1L1abcdef      L1
    FGL1xyz1234567  L1
    */

    I am not sure where you are going wrong; to me it looks like this where clause is correct:

    aString like '__' + test + '%'

    Wednesday, August 15, 2012 6:38 PM
    Moderator
  • Hi naarasimha,

    This is only correct answer. 

    Wednesday, August 15, 2012 6:39 PM
  • If you are looking for a particular set of characters (like "L1") in a particular location (like positions 3 and 4) in the string, don't use LIKE, instead use SUBSTRING, that is

    WHERE SUBSTRING(myTable.myColumn, 3, 2) = 'L1'

    Using like and the correct number of underscores will work, but seems unnecessarily complex to me.  But it does work, for example,

    declare @Test Table(MyColumn varchar(20));
    Insert @Test(MyColumn) Values
    ('C1L1abcdef'),
    ('C2L31234L1'),
    ('FGL1xyz1234567'),
    ('C1L2asdf');
    DECLARE @myString varchar(2);
    SET @myString = 'C1'; 
    SELECT 'Checking for C1', * FROM @Test WHERE MyColumn LIKE '__' + @myString + '%'; 
    SET @myString = 'L1'; 
    SELECT 'Checking for L1', * FROM @Test WHERE MyColumn LIKE '__' + @myString + '%'; 

    Tom

    Wednesday, August 15, 2012 6:45 PM