May 3, 2018 at 1:31 pm
I have a table with column data that looks like
Some text
some text (123546)
some Text (01-01-01)
Some text (ABC123)
Some Text (XYZ001) more text.
I want to select only the rows that include (XXX000) and thought I would filter them out using REGEX.
However, my SELECT Statement returns 0 rows.
SELECT * FROM #TEMP WHERE Column LIKE '%\([a-zA-Z]{3}[0-9]{3}\)%'
The expected result is for this to return the example rows:
Some text (ABC123)
Some Text (XYZ001) more text.
What am I doing wrong here?
May 3, 2018 at 1:43 pm
creamore - Thursday, May 3, 2018 1:31 PMI have a table with column data that looks likeSome text
some text (123546)
some Text (01-01-01)
Some text (ABC123)
Some Text (XYZ001) more text.I want to select only the rows that include (XXX000) and thought I would filter them out using REGEX.
However, my SELECT Statement returns 0 rows.
SELECT * FROM #TEMP WHERE Column LIKE '%\([a-zA-Z]{3}[0-9]{3}\)%'The expected result is for this to return the example rows:
Some text (ABC123)
Some Text (XYZ001) more text.What am I doing wrong here?
Try this:CREATE TABLE #TEMP (
MyColumn varchar(50)
);
INSERT INTO #TEMP (MyColumn)
VALUES ('Some text'),
('some text (123546)'),
('some Text (01-01-01)'),
('Some text (ABC123) '),
('Some Text (XYZ001) more text.');
SELECT *
FROM #TEMP
WHERE MyColumn LIKE '%([a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9][0-9])%';
DROP TABLE #TEMP;
I don't think you can just use regular expressions with SQL Server, but I'm not 100% sure on that, but the above code works, as you don't need to escape the parentheses.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 1:57 pm
Of course...
Thank you!
Now I feel like a complete dufus! LOL!
May 3, 2018 at 2:11 pm
creamore - Thursday, May 3, 2018 1:57 PMOf course...
Thank you!
Now I feel like a complete dufus! LOL!
You wouldn't be the first, nor likely the last, either. It's all a matter of exposure for most folks, and once you learn it, and what LIKE will allow, you're usually good to go. Do a quick Google search on SQL Server LIKE and find a link to Microsoft's documentation on it. It should have all the rules and expressions you can use.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply