June 7, 2020 at 10:43 pm
A developer asked me to run a query based on the following regex:
^[A-Z]{4}[0-9]{4}
Which means that he wanted any row where the column in question did not have 4 alpha characters, followed by 4 numeric characters.
I last played with regex about 6 years ago, so hunted around and found nothing suitable for T-SQL.
In the end (because it was only supposed to be quick query) I cobbled something together that gave me the data I wanted, but I wasn't happy with.
I couldn't find any definitive articles for T-SQL and regex, that helped me with this particular situation, so was wondering if anybody else could provide a neater solution.
A very basic test script is supplied below.
CREATE DATABASE Regex_Test;
GO
USE Regex_Test;
GO
CREATE TABLE dbo.TestTable
(
ID INT IDENTITY NOT NULL,
Reg_Text NVARCHAR(200)
);
GO
INSERT INTO dbo.TestTable
(
Reg_Text
)
VALUES
(N'AAAA1111'),
(N'ABCD1234'),
(N'A5'),
(N'ABCD123');
--NOT [A-Z]{4}[0-9]{4}
SELECT DISTINCT
Reg_Text
FROM dbo.TestTable
WHERE Reg_Text NOT IN
(
SELECT DISTINCT
Reg_Text
FROM dbo.TestTable
WHERE Reg_Text LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]%'
)
ORDER BY Reg_Text;
--DROP DATABASE Regex_Test;
--GO
June 7, 2020 at 10:54 pm
SELECT DISTINCT
Reg_Text
FROM dbo.TestTable
WHERE Reg_Text NOT LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]%'
June 8, 2020 at 1:03 am
in SQL the only thing that allows something nearer regex is patindex - but it is still not full regex.
give that one some time as it can do things that the like /not like can't do
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply