Query

Question

Query

satishthota

SSC Eights!

Points: 959
More actions
October 7, 2020 at 2:41 pm

#3793671

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

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

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

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)