SQL Query

Question

Post reply

SQL Query

michael.t.fisher

SSC Enthusiast

Points: 104
More actions
August 5, 2013 at 2:34 pm

#297369

Hi, In need of help with an SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
1.2 (3 characters) will not always be the same, there will be some instances of 5 characters, e.g. 2.1.1
KPIA1 (5 characters) will not always be the same, there may be some instances of 6 characters, eg KPIC10.
so, examples may include;
1.2-KPIA1-App-00001
2.1.1-KPIA2-APP-00008
2.4-KPIC10-App-00010

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

eccentricDBA SSCarpal Tunnel Points: 4844 More actions · Answer 1

So you need to split the column by the '-'?

Here is a nice article by Divya Agrawal that may help you:

http://www.sqlservercentral.com/articles/XML/66932/

michael.t.fisher SSC Enthusiast Points: 104 More actions · Answer 2

Yeah, e.g 1.2-KPIA1-App-00001. so everything before the first dash i.e. 1.2, 1.2.1, etc. Then everything after the first dash but before the second i.e. KPIA1, KPIC10, etc. Okay thanks 🙂

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

What you are describing is string parsing. While the article referenced above is ok, the one in my signature has a version that is way faster than xml parsing. I would highly recommend you read that article and use the parser found there. Assuming you do that your query would look like this.

create table #Something

(

SomeValue varchar(50)

)

insert #Something

select '1.2-KPIA1-App-00001' union all

select '2.1.1-KPIA2-APP-00008' union all

select '2.4-KPIC10-App-00010'

select *

from #Something

cross apply dbo.DelimitedSplit8K(SomeValue, '-')

Notice how I posted ddl and sample data in a readily consumable format. This is the best thing you can do for your posts so it is easy for others to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/