Sql Query Help Needed

[BC509]

CREATE FUNCTION [dbo].[Split]

(

@List nvarchar(2000),

@SplitOn nvarchar(5)

)  

RETURNS @RtnValue table 

(

 

Id int identity(1,1),

Value nvarchar(100)

) 

AS  

BEGIN

While (Charindex(@SplitOn,@List)>0)

Begin 

Insert Into @RtnValue (value)

Select

    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

End 

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

 

    Return

END

[jaffanajeffada]

 

if the format of the data stays put, below sql will give you desired output...

 

replace COLUMN_NAME and TABLE_NAME with actual names

 

SELECT COLUMN_NAME, 

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,1)+1,INSTR(COLUMN_NAME,'&',1,1)-INSTR(COLUMN_NAME,'=',1,1)-1)) PANEL,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,2)+1,INSTR(COLUMN_NAME,'&',1,2)-INSTR(COLUMN_NAME,'=',1,2)-1)) YEAR,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,3)+1,INSTR(COLUMN_NAME,'&',1,3)-INSTR(COLUMN_NAME,'=',1,3)-1)) SOURCE,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,4)+1)) USERID

FROM  TABLE_NAME

 

 

k.#4$  k.#4$

CITI#H@

[BC509]

CREATE the function try running the query .. Rather than hard coding .. use this standard practice .

[jaffanajeffada]

 

CREATE FUNCTION [dbo].[Split]

(

@List nvarchar(2000),

@SplitOn nvarchar(5)

)  

RETURNS @RtnValue table 

(

 

Id int identity(1,1),

Value nvarchar(100)

) 

AS  

BEGIN

While (Charindex(@SplitOn,@List)>0)

Begin 

Insert Into @RtnValue (value)

Select

    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

End 

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

 

    Return

END

 

:4_12_13:  :4_12_13:

[BC509]

 

if the format of the data stays put, below sql will give you desired output...

 

replace COLUMN_NAME and TABLE_NAME with actual names

 

SELECT COLUMN_NAME, 

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,1)+1,INSTR(COLUMN_NAME,'&',1,1)-INSTR(COLUMN_NAME,'=',1,1)-1)) PANEL,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,2)+1,INSTR(COLUMN_NAME,'&',1,2)-INSTR(COLUMN_NAME,'=',1,2)-1)) YEAR,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,3)+1,INSTR(COLUMN_NAME,'&',1,3)-INSTR(COLUMN_NAME,'=',1,3)-1)) SOURCE,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,4)+1)) USERID

FROM  TABLE_NAME

 

 

 

There is no SUBSTR function in SQL Server ..

[ChampakDas]

%$#$

[jaffanajeffada]

There is no SUBSTR function in SQL Server ..

thread started didn't mention platform name...

 

but good to know that SQL Server doesn't have SUBSTR... I hardly used SQL Server

[loveindia]

If it's always 4 columns, use this... Just create a function and use it..

 

DECLARE @Code VARCHAR(MAX) = 'PanelPanel=AB01110201&YearYear=2013&Source=1&UserID=aad2779', @ReplacedCode VARCHAR(MAX)
 
SELECT @ReplacedCode = REPLACE(REPLACE(REPLACE(REPLACE(@Code, 'PanelPanel=', ''), '&YearYear=','.'), '&Source=', '.'), '&UserID=', '.')
 
SELECT PARSENAME(@ReplacedCode, 4) AS Panel
,PARSENAME(@ReplacedCode, 3) AS YearYear
,PARSENAME(@ReplacedCode, 2) AS Source
,PARSENAME(@ReplacedCode, 1) AS Userid

[powerstar02]

If it's always 4 columns, use this... Just create a function and use it..

DECLARE @Code VARCHAR(MAX) = 'PanelPanel=AB01110201&YearYear=2013&Source=1&UserID=aad2779', @ReplacedCode VARCHAR(MAX)
 
SELECT @ReplacedCode = REPLACE(REPLACE(REPLACE(REPLACE(@Code, 'PanelPanel=', ''), '&YearYear=','.'), '&Source=', '.'), '&UserID=', '.')
 
SELECT PARSENAME(@ReplacedCode, 4) AS Panel
,PARSENAME(@ReplacedCode, 3) AS YearYear
,PARSENAME(@ReplacedCode, 2) AS Source
,PARSENAME(@ReplacedCode, 1) AS Userid

anni Replace lu enduku raa bujja ...:o

[loveindia]

 

CREATE FUNCTION [dbo].[Split]

(

@List nvarchar(2000),

@SplitOn nvarchar(5)

)  

RETURNS @RtnValue table 

(

 

Id int identity(1,1),

Value nvarchar(100)

) 

AS  

BEGIN

While (Charindex(@SplitOn,@List)>0)

Begin 

Insert Into @RtnValue (value)

Select

    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

End 

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

 

    Return

END

 

 

Split works fine but in the query its not always the same to split on, so that causes extra headache for the scalar function... And scalar functions are very noted for killing performance.. 

[loveindia]

anni Replace lu enduku raa bujja ... :o

 

lekunda nuvvu raayochu kada anniyaaa.... nerchukuntam andaram bacha gallam... megastar zindabad :(

[powerstar02]

lekunda nuvvu raayochu kada anniyaaa.... nerchukuntam andaram bacha gallam... megastar zindabad :(

baa vurke annanu ...em aypoyav ..nuvu leni ee db ...:(

[loveindia]

baa vurke annanu ...em aypoyav ..nuvu leni ee db ... :(

 

PRP leni politics aa anniya???  :(

[powerstar02]

PRP leni politics aa anniya??? :(

Annaya leni industry ...:(