October 7, 2020 at 2:41 pm
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
October 7, 2020 at 2:54 pm
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 7, 2020 at 2:56 pm
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
October 7, 2020 at 6:04 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply