Query

Question

Post reply

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 SSCommitted Points: 1686 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

ItemNumber	Item	        display_val
1	        ID=205	        205
1	        ID=25	        25
1	        ID=2	        2
1	        ID=2006	        2006

This reply was modified 3 years, 11 months 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: 245813 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;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions