August 31, 2009 at 4:34 am
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
August 31, 2009 at 4:38 am
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
August 31, 2009 at 4:45 am
Try
SELECT
ID, Seq
FROM
Sequence
WHERE
Seq = REPLICATE('1', LEN(Seq))
Peter
August 31, 2009 at 4:57 am
naveenreddy.84 (8/31/2009)
Please find below the table creation and Insert ScriptCREATE 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/61537August 31, 2009 at 5:50 am
Thanks, exactly what i needed!!! thank a lot
August 31, 2009 at 7:14 am
Mark (8/31/2009)
naveenreddy.84 (8/31/2009)
Please find below the table creation and Insert ScriptCREATE 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"
---------------------------------------------------------------------------------
August 31, 2009 at 7:40 am
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
August 31, 2009 at 7:40 am
Pakki (8/31/2009)
Mark (8/31/2009)
naveenreddy.84 (8/31/2009)
Please find below the table creation and Insert ScriptCREATE 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/61537August 31, 2009 at 7:45 am
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