CODEWITHSUNDEEP
sql-server
Peter Hansen
Peter Hansen

Reputation: 81

Regex 2 letters and 3, 4 or 5 digits

Can I do a regex in a SP in SQL Server and catch part of a string than contains 2 letters and 3, 4 or 5 digits?

The string can be like this:

"Joe Brown AB12345 21223355"

or

"Joe Brown AB1234 21223355"

or

"Joe Brown AB123 21223355"

And I want to find AB12345, AB1234, AB123

I have tried with this:

SUBSTRING(Name, (PATINDEX('%[A-Za-z][A-Za-z][0-9][0-9][0-9][0-9]%',[Name])),7)

Upvotes: 1

Views: 3301

Answers (2)

shawnt00
shawnt00

Reputation: 17915

Not the prettiest, but should work...

declare @s varchar(32) = 'select [NAME] from ...';
select case
    when patindex('%[A-Za-z][A-Za-z][0-9][0-9][0-9][0-9]%', @s) > 0
    then substring(@s, patindex('%[A-Za-z][A-Za-z][0-9][0-9][0-9][0-9]%', @s), 7)
    when patindex('%[A-Za-z][A-Za-z][0-9][0-9][0-9]%', @s) > 0
    then substring(@s, patindex('%[A-Za-z][A-Za-z][0-9][0-9][0-9]%', @s), 6)
    when patindex('%[A-Za-z][A-Za-z][0-9][0-9]%', @s) > 0
    then substring(@s, patindex('%[A-Za-z][A-Za-z][0-9][0-9]%', @s), 5)
end

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 3

Related Questions