August 5, 2013 at 2:34 pm
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
August 5, 2013 at 2:46 pm
So you need to split the column by the '-'?
Here is a nice article by Divya Agrawal that may help you:
August 5, 2013 at 2:49 pm
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
August 5, 2013 at 2:51 pm
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy