x

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).

more ▼

asked Feb 02, 2017 at 01:02 PM in Default

avatar image

d9maglicco
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Feb 02, 2017 at 07:59 PM

avatar image

Oleg
19.8k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x21
x12

asked: Feb 02, 2017 at 01:02 PM

Seen: 36 times

Last Updated: Feb 02, 2017 at 07:59 PM

Copyright 2018 Redgate Software. Privacy Policy