asked
How to trim a string with no pattern?
I have data that looks like this: BCR16-100MIN.sdm PML15-80INT6.sdm BCR16-70min I need to parse it out so it looks like this: 16-100 15-80 16-70 I tried a function to get just numeric values then realized that posed an issue for the example: PML15-80INT6.sdm as it displayed 15806 (I can get the hyphen as i know the 1st 2 numbers represent a year and will never be more than 2).
It looks like the values are pretty consistent and so you can take 2 numbers and dash and 2 more numbers first. After that check the next character and if it is numeric then add it to what you have so far. Here is an example which includes mock up of the data on first 2 lines: -- mockup some sample data declare @t table (ValueToParse varchar(20)); insert into @t values ('BCR16-100MIN.sdm'), ('PML15-80INT6.sdm'), ('BCR16-70min'); select ValueToParse, substring(ValueToParse, 4, 5) + case when isnumeric(substring(ValueToParse, 9, 1)) = 1 then substring(ValueToParse, 9, 1) else '' end ParsedValue from @t; -- results: ValueToParse ParsedValue -------------------- ----------- BCR16-100MIN.sdm 16-100 PML15-80INT6.sdm 15-80 BCR16-70min 16-70 Hope this helps. Oleg
18 People are following this question.
