Sql Server 2016 How To Use A Simple Regular Expression In T-sql?

I have a column with the name of a person in the following format: 'LAST NAME, FIRST NAME' Only Upper Cases Allowed Space after comma optional I would like to use a regular expre

Solution 1:

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@tabletable (Person varchar(64), is_correct_format varchar(3) default'NO')
insertinto@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@tableset is_correct_format ='YES'where 
        Person notlike'%[^A-Z, ]%'--check for non characters, excluding comma and spacesand len(replace(Person,' ','')) = len(replace(replace(Person,' ',''),',','')) +1--make sure there is only one commaand charindex(',',Person) <>1--make sure the comma isn't at the beginningand charindex(',',Person) <> len(Person)                                            --make sure the comma isn't at the endandsubstring(Person,charindex(',',Person) -1,1) <>' '--make sure there isn't a space before commaandleft(Person,1) <>' '--check preceeding spacesandUPPER(Person) = Person collate Latin1_General_CS_AS                             --check collation for CI default (only upper cases)select*from@table

Solution 2:

The tsql equivalent could look like this. I'm not vouching for the efficiency of this solution.

declare@tableastable(name varchar(20), is_Correct_format varchar(5))
insertinto@table(name) Values
('Smith, Jon')
,('se7en, six')
,('Billy bob')


UPDATE@tableSET is_correct_format ='YES'WHERE
replace(name, ', ', ',x')
     like (replicate('[a-z]', charindex(',', name) -1)
         +','+ replicate('[a-z]', len(name) - charindex(',', name)) )


select*from@table

The optional space is hard to solve, so since it's next to a legal character I'm just replacing with another legal character when it's there.

TSQL does not provide the kind of 'repeating pattern' of * or + in regex, so you have to count the characters and construct the pattern that many times in your search pattern.

I split the string at the comma, counted the alphas before and after, and built a search pattern to match.

Clunky, but doable.