Hi,
Required an urgent reply - Have to convert HTTP query string to SQL table.
Ex:
serialN0=101&employeeId=ITS1026&Name=XXXXX
Need the output from Query as
[TableA]
[serialNo] [employeeId] [Name]
101 ITS1026 XXXXX
Hi,
Required an urgent reply - Have to convert HTTP query string to SQL table.
Ex:
serialN0=101&employeeId=ITS1026&Name=XXXXX
Need the output from Query as
[TableA]
[serialNo] [employeeId] [Name]
101 ITS1026 XXXXX
Here's one way:
declare @str varchar(255) = 'serialN0=101&employeeId=ITS1026&Name=XXXXX'
select substring(@str, first_eq+1, first_amp-first_eq -1) as SerialNo
, substring(@str, second_eq+1, second_amp-second_eq -1) as EmployeeID
, substring(@str, third_eq+1, len(@str)) as Name
from (values (@str)) v(s)
cross apply (select CHARINDEX('=', s, 1)) #1(first_eq)
cross apply (select CHARINDEX('&', s, first_eq+1)) #2(first_amp)
cross apply (select CHARINDEX('=', s, first_amp+1)) #3(second_eq)
cross apply (select CHARINDEX('&', s, second_eq+1)) #4(second_amp)
cross apply (select CHARINDEX('=', s, second_amp+1)) #5(third_eq)
cross apply (select CHARINDEX('&', s, third_eq+1)) #6(third_amp)
Another way
declare @q as varchar(255)
set @q='serialN0=1011&employeeId=ITS10226&Name=XXXXXX'
select 'serialNo'= SUBSTRING(@q, CHARINDEX( '=',@q) + 1, CHARINDEX('&', @q) - CHARINDEX( '=',@q) -1 )
, 'EmployeeId'= SUBSTRING(@q, PATINDEX('%Id=%',@q) + 3, PATINDEX('%&N%',@q)- PATINDEX('%Id=%',@q) -3 )
, 'Name' = SUBSTRING ( REVERSE(@q), 0, CHARINDEX ( '=',REVERSE(@q)) ) into #TblFinal