Using RegEx in SQL Server
You do not need to interact with managed code, as you can use LIKE:
CREATE TABLE #Sample(Field varchar(50), Result varchar(50))
GO
INSERT INTO #Sample (Field, Result) VALUES ('ABC123 ', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123.', 'Do not match')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123&', 'Match')
SELECT * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'
GO
DROP TABLE #Sample
As your expression ends with +
you can go with '%[^a-z0-9 .][^a-z0-9 .]%'
EDIT:
To make it clear: SQL Server doesn't support regular expressions without managed code. Depending on the situation, the LIKE
operator can be an option, but it lacks the flexibility that regular expressions provides.
Regex 2 letters and 3, 4 or 5 digits
One option is to use string_split().
Note the last two OR's may not be necessary, just more precise.
Example
Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values
('Joe Brown AB12345 21223355')
,('Joe Brown AB1234 21223355')
,('Joe Brown AB123 21223355')
Select *
From @YourTable A
Cross Apply string_split(SomeCol,' ') B
Where B.value like '[A-Z][A-Z][0-9][0-9][0-9]'
or B.value like '[A-Z][A-Z][0-9][0-9][0-9][0-9]'
or B.value like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]'
Returns
SomeCol value
Joe Brown AB12345 21223355 AB12345
Joe Brown AB1234 21223355 AB1234
Joe Brown AB123 21223355 AB123
SQL Regex Expression
select *
from
(
values ('ABC-12345'), ('AB-123'), ('A-12345'), ('A1B-1234'), ('ABC12345'), ('xyz-12'), ('klmno-12345'), ('abc.12345')
) as t(v)
where v like '[A-Z]%-%[0-9]' --values start with letter & contain a hyphen & end with a digit
--left part
and len(substring(v, 1, charindex('-', v)-1)) between 1 and 3 --the left part is 1-3 chars long
and substring(v, 1, charindex('-', v)-1) not like '%[^A-Z]%'--the left part has only letters
--right part
and len(stuff(v, 1, charindex('-', v), '')) between 3 and 5 --right part is 3-5 chars long
and stuff(v, 1, charindex('-', v), '') not like '%[^0-9]%'; --and it contains only digits
MSSQL Regular expression
This is what I have used in the end:
SELECT *,
CASE WHEN [url] NOT LIKE '%[^-A-Za-z0-9/.+$]%'
THEN 'Valid'
ELSE 'No valid'
END [Validate]
FROM
*table*
ORDER BY [Validate]
Complex RegEx in T-SQL
I would recommend writing a user defined function using SQLCLR. Since .Net supports Regex you can port it to T-SQL. First link in Google gave this implementation, but there may be other (better) implementations.
Caveat - use of SQLCLR requires elevated permissions and may lead to security issues or performance issues or even issues with stability of the SQL Server if not implemented correctly. But if you know what you are doing this may lead to significant enhancements of T-SQL specific for your use cases.
SQL Server regex alternation
SQL Server does not support formal regex, so you will have to use LIKE
here:
SELECT TOP 100 *
FROM posts
WHERE tags LIKE '%c++%' OR tags LIKE '%performance%';
Note: If the tags
column really stores one tag per record, then just use equality checks:
SELECT TOP 100 *
FROM posts
WHERE tags IN ('%c++', 'performance');
Regex in SQL Server, how can i found only certain length of integers
SQL Server does not have Regex. Even if it did, I wouldn't recommend it here
You can just use a cast and BETWEEN
WHERE TRY_CAST(YourColumn AS int) BETWEEN 100 AND 999
If the column is actually an int
then you don't even need a cast.
SQL Server telephone regex check
SQL Server does not support Regex natively. It does have pattern matching, however, the functionality is no where near that of Regex.
For what you have you would need to do:
SELECT PhoneNumber
FROM Sales.Customers
WHERE PhoneNumber NOT LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]';
Edit: OP has now edited to LIKE
(was NOT LIKE
). Not sure which they want, so I have left as NOT LIKE
, as per their original version.
T-SQL Equivalent of regular expression '\b'
I think pattern '%"Id":1234[^a-zA-Z0-9]%'
will do.
It uses negated character class [^a-zA-Z0-9]
, which works as in regular regex :)
Sample:
declare @tbl table (col varchar(100));
insert into @tbl values
('{"Id":1234, "Title": "The quick brown"}'),
('{"Id":1234, "Title": "The quick brown"}'),
('Id":1234, "Title": "The quick brown"}'),
('{"Id":12345, "Title": "The quick brown"}');
select *
from @tbl
where col like '%"Id":1234[^a-zA-Z0-9]%'
Related Topics
SQL Identity with Leading Padded Zeros
What Happens with Duplicates When Inserting Multiple Rows
Finding Continuous Ranges in a Set of Numbers
How to Prevent SQL Injection in Wordpress
Transpose Rows into Columns in SQL Server 2008 R2
Microsoft Access Query Should Return True or True and False, Only Returns True
Postgresql - Order by an Array
Maximum and Minimum Number of Tuples in Natural Join
F# Type Provider for SQL in a Class
Interview - Detect/Remove Duplicate Entries
How to Create a Dates Table in Redshift
SQL Server, Combining Like and In
SQL Aggregate Function to Obtain a List