SQLTeam.com | Weblogs | Forums

HTTP Query String Text to SQL Table


#1

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


#2

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)

#3

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