May 31, 2019 at 5:19 am
Hello Members,
I have a column containing string values. I want to fetch all the values for which below conditions are met.
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
May 31, 2019 at 9:14 am
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
May 31, 2019 at 1:13 pm
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
June 3, 2019 at 4:58 am
@Taps, Yes we are doing the same. Thank you for your reply.
Regards,
Akash
June 10, 2019 at 9:49 am
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 *******
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply