question

d9maglicco avatar image
d9maglicco 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).
parsingstring-function
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.