Pattern matching in TSql

• naveenreddy.84

  Ten Centuries

  Points: 1252

  More actions

  August 31, 2009 at 4:34 am

  #134649

  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

• naveenreddy.84

  Ten Centuries

  Points: 1252

  More actions

  August 31, 2009 at 4:38 am

  #1046789

  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

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  August 31, 2009 at 4:45 am

  #1046792

  Try

  SELECT

  ID, Seq

  FROM

  Sequence

  WHERE

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

  Peter

• Mark Cowne

  One Orange Chip

  Points: 26951

  More actions

  August 31, 2009 at 4:57 am

  #1046794

  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

• naveenreddy.84

  Ten Centuries

  Points: 1252

  More actions

  August 31, 2009 at 5:50 am

  #1046803

  Thanks, exactly what i needed!!! thank a lot

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  August 31, 2009 at 7:14 am

  #1046857

  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"

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

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  August 31, 2009 at 7:40 am

  #1046868

  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]

• Mark Cowne

  One Orange Chip

  Points: 26951

  More actions

  August 31, 2009 at 7:40 am

  #1046869

  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

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  August 31, 2009 at 7:45 am

  #1046872

  Thanks Garadin and thanks Mark. We would have to work our way out using these two operators!!!!.

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