Pattern matching in TSql

  • Hi,

    This is regarding pattern matching in Tsql Query

    I have table with the following columns and corresspoding sample values

    ID Seq

    1 01111

    2 10111

    3 11011

    4 11101

    5 11110

    6 11111

    I need to query for the seq value which are all 1's .In this example, the query should return the value 11111.

    kindly suggest if i can queries this with pattern matching.

    Regards,

    Naveen

  • Please find below the table creation and Insert Script

    CREATE TABLE Sequence

    (ID INT,

    Seq VARCHAR(MAX))

    INSERT INTO Sequence VALUES(1,'01111')

    INSERT INTO Sequence VALUES(1,'10111')

    INSERT INTO Sequence VALUES(1,'11011')

    INSERT INTO Sequence VALUES(1,'11101')

    INSERT INTO Sequence VALUES(1,'11110')

    INSERT INTO Sequence VALUES(1,'11111')

    Thanks

    Naveen

  • Try

    SELECT

    ID, Seq

    FROM

    Sequence

    WHERE

    Seq = REPLICATE('1', LEN(Seq))

    Peter

  • naveenreddy.84 (8/31/2009)


    Please find below the table creation and Insert Script

    CREATE TABLE Sequence

    (ID INT,

    Seq VARCHAR(MAX))

    INSERT INTO Sequence VALUES(1,'01111')

    INSERT INTO Sequence VALUES(1,'10111')

    INSERT INTO Sequence VALUES(1,'11011')

    INSERT INTO Sequence VALUES(1,'11101')

    INSERT INTO Sequence VALUES(1,'11110')

    INSERT INTO Sequence VALUES(1,'11111')

    Thanks

    Naveen

    SELECT ID,Seq

    FROM Sequence

    WHERE Seq NOT LIKE '%0%'

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks, exactly what i needed!!! thank a lot

  • Mark (8/31/2009)


    naveenreddy.84 (8/31/2009)


    Please find below the table creation and Insert Script

    CREATE TABLE Sequence

    (ID INT,

    Seq VARCHAR(MAX))

    INSERT INTO Sequence VALUES(1,'01111')

    INSERT INTO Sequence VALUES(1,'10111')

    INSERT INTO Sequence VALUES(1,'11011')

    INSERT INTO Sequence VALUES(1,'11101')

    INSERT INTO Sequence VALUES(1,'11110')

    INSERT INTO Sequence VALUES(1,'11111')

    Thanks

    Naveen

    SELECT ID,Seq

    FROM Sequence

    WHERE Seq NOT LIKE '%0%'

    Hi Mark, do you know if there is something similar to 'Regular expression' (in dot net) in SQL Server for pattern matching? I guess this kind of validation has to be handled in the front end but was just curious to know if there is a way to do in SQL Server. Lets say it has to look for something in this format "123-4567-89.123"

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

  • Pakki,

    You can do something like this:

    DECLARE @TestSeq VARCHAR(20)

    SET @TestSeq = '123-4567-89.123'

    SELECT CASE WHEN @TestSeq LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9].[0-9][0-9][0-9]'

    THEN 'Passed'

    ELSE 'Failed'

    END

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Pakki (8/31/2009)


    Mark (8/31/2009)


    naveenreddy.84 (8/31/2009)


    Please find below the table creation and Insert Script

    CREATE TABLE Sequence

    (ID INT,

    Seq VARCHAR(MAX))

    INSERT INTO Sequence VALUES(1,'01111')

    INSERT INTO Sequence VALUES(1,'10111')

    INSERT INTO Sequence VALUES(1,'11011')

    INSERT INTO Sequence VALUES(1,'11101')

    INSERT INTO Sequence VALUES(1,'11110')

    INSERT INTO Sequence VALUES(1,'11111')

    Thanks

    Naveen

    SELECT ID,Seq

    FROM Sequence

    WHERE Seq NOT LIKE '%0%'

    Hi Mark, do you know if there is something similar to 'Regular expression' (in dot net) in SQL Server for pattern matching? I guess this kind of validation has to be handled in the front end but was just curious to know if there is a way to do in SQL Server. Lets say it has to look for something in this format "123-4567-89.123"

    LIKE and PATINDEX use regular expressions.

    For example, for "123-4567-89.123", you could use

    LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9].[0-9][0-9][0-9]'

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks Garadin and thanks Mark. We would have to work our way out using these two operators!!!!.

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

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

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