1

How to achieve below requirement in SQL Server.

Data what I have:

Table A(Input

and Expected output is:

Table B(Expected Output

Thanks, Lawrance A

Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Lawrance Amburose
  • 155
  • 1
  • 10
  • You absolutely should be using a JSON parser for this. If SQL Server supports JSON extensions, then use those; if not, then parse the JSON elsewhere and import it into SQL Server. – Tim Biegeleisen Nov 09 '18 at 06:31

2 Answers2

0

Assuming of your given data value:

 'Message:"A",Level:"0",type:"log"'
 'Message:"B",Level:"1",type:"log"'

select substring(ColumnA,10,1) Message,
replace(dbo.udf_GetNumeric(replace(replace(substring(ColumnA,20,5),'"',''),',','')),' ','')[type],
replace(replace(substring(ColumnA,29,250),'"',''),',','')[Log] from YourTable

GetNumeric here

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
0

I have created two function to achieve your desired output. use below query by creating two function split and getVal

select 
    dbo.getVal(columnA,'Message') Message,
    dbo.getVal(columnA,'Level') [Level],
    dbo.getVal(columnA,'type') [type]
from TableA

Query for creating split and getVal function 

Create function split(@s varchar(500),@splitWith varchar(20))
returns @RetTable Table(id int identity(1,1), Value varchar(200))
as
begin
    if(CHARINDEX(@splitWith,@s)>0)
    begin
    set @s=@s+@splitWith
        declare @len int =len(@s)
        while charindex(@splitWith,@s)>0
        begin
            insert @RetTable values(SUBSTRING(@s,1,charindex(@splitWith,@s)-1))
            set @s=SUBSTRING(@s,charindex(@splitWith,@s,1)+1,@len)
        end
    end
    return
end



 Create function getVal(@str varchar(500),@column varchar(200))
    returns varchar(200)
    as
    begin
    declare @ret varchar(200)
        select @ret=value From dbo.split((select value From dbo.split('Message:"A",Level:"0",type:"log"',',') where value like +@column+'%'),':') where id=2
        return replace(@ret,'"','')
    end
Sushil Sharma
  • 81
  • 9