SQL Query

  • 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

  • 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/

  • 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 🙂

  • 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