Query

  • How to show below result in sql

    ID=205&SID=1005&TID=3

    ID=25&SID=1005&TID=3

    ID=2&SID=1005&TID=3

    ID=2006&SID=1005&TID=3

    Result

    205

    25

    2

    2006

     

     

     

  • Please have a look at this article about string splitters.  I think you're looking for something like this

    drop table if exists #stuff;
    go
    create table #stuff(
    sometext varchar(1000) not null);

    insert #stuff(sometext) values
    ('id=205&sid=1005&tid=3')
    ,('id=25&sid=1005&tid=3')
    ,('id=2&sid=1005&tid=3')
    ,('id=2006&sid=1005&tid=3');

    select ds.*, right(ds.item, len(ds.item)-3) display_val
    from #stuff s
    cross apply dbo.DelimitedSplit8K(s.sometext, '&') ds
    where item like 'ID=%';

    Output

    ItemNumberItem        display_val
    1 ID=205 205
    1 ID=25 25
    1 ID=2 2
    1 ID=2006 2006

    • This reply was modified 4 years, 1 month ago by  Steve Collins. Reason: Updated with Phil's sample data

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Here's another way

    DROP TABLE IF EXISTS #Stuff;

    CREATE TABLE #Stuff
    (
    SomeText VARCHAR(1000) NOT NULL
    );

    INSERT #Stuff
    (
    SomeText
    )
    VALUES
    ('ID=205&SID=1005&TID=3')
    ,('ID=25&SID=1005&TID=3')
    ,('ID=2&SID=1005&TID=3')
    ,('ID=2006&SID=1005&TID=3');

    SELECT s.SomeText
    ,Extracted = SUBSTRING(s.SomeText, pos.StartPos, pos.EndPos - pos.StartPos)
    FROM #Stuff s
    CROSS APPLY
    (
    SELECT StartPos = CHARINDEX('=', s.SomeText) + 1
    ,EndPos = CHARINDEX('&', s.SomeText)
    ) pos;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • If the format is guaranteed an you only want the first element, then the code can be greatly simplified.

     SELECT Result = SUBSTRING(SomeText,4,CHARINDEX('&',SomeText,4)-4)
    FROM #Stuff
    WHERE SomeText LIKE 'ID=%'
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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