Post reply

How to get string after Some string in text

  • August 20, 2012 at 12:23 pm

    #265486

    I have a table like-

    S.No text

    1 I added some subjects Arts(Subject Number 0919), Science(Subject Number 0129), Maths(Subject Number 9008), Embedded(Subject Number 0987).....

    2 I added some subjects Maths(Subject Number 1998), Analytics(Subject Number 0988).......

    3 I added some subject Electrical(Subject Number 7878),.......

    Now, I need to extract all subject codes in each rows. and store them under each S.No. Only hint is that code follows the string "Subject Number". I dont know how many codes present in each row.

    My desire output

    S.No Subject Code

    1 Arts 0919

    1 Science 0129

    1 Maths 9008

    1 Embedded 0987

    2 Maths 1998

    2 Analytics 0988

    3 Electrical 7878

    Can Anyone please suggest me tha best approach to get this.

    Thanks in Advance!

    Kumar

  • August 20, 2012 at 12:44 pm

    #1527791

    well the function dbo.DelimitedSplit8K can get you about 90% of the way there; you'd have to clean it up a bit.

    you have to be able to find a delimiter in the string.

    by splitting ont eh comma, you get most of it, but there's a lot of extra stuff.

    see if this gets you close(get teh function by searching SSC!)

    /*--Results

    I added some subjects Arts(Subject Number 0919)

    Science(Subject Number 0129)

    Maths(Subject Number 9008)

    Embedded(Subject Number 0987).....

    I added some subjects Maths(Subject Number 1998)

    Analytics(Subject Number 0988).......

    I added some subject Electrical(Subject Number 7878)

    .......

    */

    with myCTE([S.No], [text])

    AS

    (

    SELECT 1,'I added some subjects Arts(Subject Number 0919), Science(Subject Number 0129), Maths(Subject Number 9008), Embedded(Subject Number 0987).....' UNION ALL

    SELECT 2,'I added some subjects Maths(Subject Number 1998), Analytics(Subject Number 0988).......' UNION ALL

    SELECT 3,'I added some subject Electrical(Subject Number 7878),.......'

    )

    select * FROM MYCTE

    CROSS APPLY dbo.delimitedSplit8K([text],',') x

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 20, 2012 at 12:45 pm

    #1527793

    You will improve the chance of someone providing you with a tested answer if you help them help you.

    To do this please click on the first link in my signature block to post the table definition and your sample data in a readily consumable format ..

    Oops see that Lowell did the work that I was asking you to do ... so forget this post of mine

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • August 20, 2012 at 12:49 pm

    #1527797

    Do you have a table with what the subject numbers mean? Like 0919 is Arts, and so on?

    If so, that will make this much, much easier. Throw a full-text index on the column, do a Contains query against the subject numbers table, and you're done.

    If not, this becomes much more complex.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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