Pattern Matching using PATINDEX

Question

Post reply

Pattern Matching using PATINDEX

akash_singh

Ten Centuries

Points: 1089

May 31, 2019 at 5:19 am

#3648807

Hello Members,

I have a column containing string values. I want to fetch all the values for which below conditions are met.

First, Second, Third, and Fourth character should be non-numeric.
Fifth and Sixth character should be numeric and greater than 12.
Six characters from the Fifth position should be numeric.
The twelveth character should be R.
Last two character should be numeric.

I was trying below using PATINDEX but it failed for "20" at fifth and sixth position. Please help.

select substring('GUDR150305R02',1,1)--must be non-numeric
select substring('GUDR150305R02',2,1)--must be non-numeric
select substring('GUDR150305R02',3,1)--must be non-numeric
select substring('GUDR150305R02',4,1)--must be non-numeric
select substring('GUDR150305R02',5,2)--must be a number > 12
select substring('GUDR150305R02',5,6)--must be numeric
select substring('GUDR150305R02',11,1)--must be 'R'
select substring('GUDR150305R02',12,2)--must be numeric

declare @a varchar(100) = 'GUDR150305R02'
--IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][120000-999999][R][0-9][0-9]',@a)>0
IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][1-9][3-9][0-9][0-9][0-9][0-9][R][0-9][0-9]',@a)>0 
Print 'T'
Else
Print 'F'

Regards,

Akash

This topic was modified 5 years, 5 months ago by akash_singh.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

I would add another condition to it

declare @a varchar(100) = 'GUDR200305R02'
--IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][120000-999999][R][0-9][0-9]',@a)>0
IF PATINDEX('[^0-9][^0-9][^0-9][^0-9][0-9][0-9][0-9][0-9][0-9][0-9][R][0-9][0-9]',@a)>0  AND CAST(SUBSTRING(@a,5,2) AS INT) >= 12
Print 'T'
Else
Print 'F'

Thanks

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 2

I like @Taps' solution, alternative is to add another check where you fix the 5th and 6th positions to be less than 12

AND PATINDEX('[^0-9][^0-9][^0-9][^0-9][0-1][^0-1][0-9][0-9][0-9][0-9][R][0-9][0-9]',@a)<=0

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

akash_singh Ten Centuries Points: 1089 More actions · Answer 3

@Taps, Yes we are doing the same. Thank you for your reply.

Regards,

Akash

akash_singh Ten Centuries Points: 1089 More actions · Answer 4

akash_singh

Ten Centuries

Points: 1089

June 3, 2019 at 6:13 am

#3649540

@Jonathan.crawford, Thank you!

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

you could look at incorporating isnumeric

declare @a varchar(100) = 'GUDR150305R02'
IF isnumeric(substring('GUDR150305R02',1,1))=0
Print 'T'
Else
Print 'F'

declare @a varchar(100) = 'GUDR150305R02'
select substring('GUDR150305R02',5,6)
IF isnumeric(substring('GUDR150305R02',5,6))=1
Print 'T'
Else
Print 'F'

***The first step is always the hardest *******