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 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
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.
SQL Server 2016 How to use a simple Regular Expression in T-SQL?
First, case sensitivity depends on the collation of the DB, though with LIKE
you can specify case comparisons. With that... here is some Boolean logic to take care of the cases you stated. Though, you may need to add additional clauses if you discover some bogus input.
declare @table table (Person varchar(64), is_correct_format varchar(3) default 'NO')
insert into @table (Person)
values
('LowerCase, Here'),
('CORRECTLY, FORMATTED'),
('CORRECTLY,FORMATTEDTWO'),
('ONLY FIRST UPPER, LowerLast'),
('WEGOT, FormaNUMB3RStted'),
('NoComma Formatted'),
('CORRECTLY, TWOCOMMA, A'),
(',COMMA FIRST'),
('COMMA LAST,'),
('SPACE BEFORE COMMA , GOOD'),
(' SPACE AT BEGINNING, GOOD')
update @table
set is_correct_format = 'YES'
where
Person not like '%[^A-Z, ]%' --check for non characters, excluding comma and spaces
and len(replace(Person,' ','')) = len(replace(replace(Person,' ',''),',','')) + 1 --make sure there is only one comma
and charindex(',',Person) <> 1 --make sure the comma isn't at the beginning
and charindex(',',Person) <> len(Person) --make sure the comma isn't at the end
and substring(Person,charindex(',',Person) - 1,1) <> ' ' --make sure there isn't a space before comma
and left(Person,1) <> ' ' --check preceeding spaces
and UPPER(Person) = Person collate Latin1_General_CS_AS --check collation for CI default (only upper cases)
select * from @table
SQL Query with Regular Expression in LIKE function
I think you need to tweak the search for "F Risk":
SELECT *
FROM TestDesc
WHERE [desc] LIKE '%Risk%' AND [desc] NOT LIKE '%[ (]F Risk%'
How to get part of string that matches with regular expression in SQL Server
Create function ReturnPart (@val as varchar(100))
Returns varchar(10)
as
begin
declare @length as int
declare @loop as int
declare @ret as varchar(10)
set @loop =1
set @length = len(@val)
while @loop<=@length-9
begin
if exists(select 1 where substring(@val,@loop,10) like '[a-z][a-z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
set @ret = substring(@val,@loop,10)
set @loop=@loop+1
end
Return @ret
end
select dbo.ReturnPart('Worked on AB-0012589')
select dbo.ReturnPart('AB-0012589 is completed')
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 Server Row_Number() on SQL Server 2000
Datareader.Getstring() via Columnname
SQL Server Displaying Missing Dates
How to Add a Column That Doesn't Allow Nulls in a Postgresql Database
How to Remove the First Characters of a Specific Column in a Table
SQL Function as Default Parameter Value
Is a Primary Key Necessary in SQL Server
SQL Pulling a Row for Next or Previous Row of a Current Row
How to Create a Table Alias in MySQL
How to Dynamically Use Tg_Table_Name in Postgresql 8.2
Fetch Records That Are Non Zero After the Decimal Point in Postgresql
Function Return Sys_Refcursor Call from SQL with Specific Columns