How to parse a string for specific values using PATINDEX and SUBSTRING?-CodePudding

I have the following example:

declare @test1 varchar(max) = 'Month 05/2022, Ord195506 Cst373175'
declare @test2 varchar(max) = 'Month 05/2022, Ord195506 Cst373175, something...'

select SUBSTRING(@test, PATINDEX('%Ord[0-9][0-9][0-9][0-9][0-9][0-9]%', @test1)   3, 6)

I need to get the following values:

05/2022
195506
373175

It must be noted that the numbers behind Ord and Abo are not fixed to a length of 6. They can be from 1 to any number.

Is it even possible to split the string into required values without a multitude of PATINDEX, SUBSTRING and RIGHT?

CodePudding user response：

For the example in your question you could string_split the elements in the string on the space and then remove the non-numeric elements using translate, such as:

declare @test1 varchar(max) = 'Month 05/2022, Ord195506 Cst373175'

select result
from (select @test1)t(test)
cross apply(
  select Trim(Translate([value],'abcdefghijklmnopqrstuvwxyz,',Space(27))) result
    from String_Split(test, ' ')
)s
where result !='';

Example DB<>Fiddle