Aleks
asked on
Select to comma delimited
I have a field with a string, I need to run a query and break down the results:
The field has the following value:
Opaque=1;Font=Times New Roman;FontSize=10;FontBold =0;FontIta lic=0;Font Underline= 0;TextColo r=0,0,0;Ba ckColor=25 5,255,204; Printable= 1
I tried the following query but it doesnt return the values I want, in fact returns nothing, I am trying to create a recordset in dreamweaver, but I got no results back. I need to do the same with each and every item in the string: Fontsize, fontbold, etc.
What I need is to be able to select the value after the = sign and before the ; sign. after the name of the parameter (fontsize, etc).
I tried the following but it didnt work:
SELECT SUBSTRING(ParamName, CHARINDEX('FontItalic=', ParamName) + 11, 1) as fontitalic FROM dbo.ParametersUsers
Aleks
The field has the following value:
Opaque=1;Font=Times New Roman;FontSize=10;FontBold
I tried the following query but it doesnt return the values I want, in fact returns nothing, I am trying to create a recordset in dreamweaver, but I got no results back. I need to do the same with each and every item in the string: Fontsize, fontbold, etc.
What I need is to be able to select the value after the = sign and before the ; sign. after the name of the parameter (fontsize, etc).
I tried the following but it didnt work:
SELECT SUBSTRING(ParamName, CHARINDEX('FontItalic=', ParamName) + 11, 1) as fontitalic FROM dbo.ParametersUsers
Aleks
i think the separator you are using is ; the function i posted uses comma(,) as separator, just change it to ;
and if you want the results to limit to read 'FontItalic=' param only
SELECT LISTITEMS.ListValue FROM dbo.ParametersUsers
outer apply [dbo].[GetListFromCSVStrin g](ParamNa me) as LISTITEMS
where LISTITEMS.ListValue like 'FontItalic=%'
and if you want the results to limit to read 'FontItalic=' param only
SELECT LISTITEMS.ListValue FROM dbo.ParametersUsers
outer apply [dbo].[GetListFromCSVStrin
where LISTITEMS.ListValue like 'FontItalic=%'
ASKER
I tried the second solution got this error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'outer'.
A
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'outer'.
A
what is your sql server version? outer apply works in 2005 only
ASKER
MS SQL 2000
try like this
in GetParamsFromTable() function you need to make few changes
change id in "Select id, ParamName from ParametersUsers" to whatever pkey ParametersUsers has.
if ParametersUsers has pkey with multiple columns then add them and change fetch statements and the out table params to reflect your table structure, or post your table structure here.
create function GetParamsFromTable()
returns @params table(id int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select id, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin g](@paramN ame)
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
ALTER FUNCTION [dbo].[GetListFromCSVStrin g]
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo s +1) )
end
Set @prevCurPos = @curPos+1
end
return
End
GO
and execute the following sql
SELECT * FROM GetParamsFromTable()
in GetParamsFromTable() function you need to make few changes
change id in "Select id, ParamName from ParametersUsers" to whatever pkey ParametersUsers has.
if ParametersUsers has pkey with multiple columns then add them and change fetch statements and the out table params to reflect your table structure, or post your table structure here.
create function GetParamsFromTable()
returns @params table(id int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select id, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
ALTER FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo
end
Set @prevCurPos = @curPos+1
end
return
End
GO
and execute the following sql
SELECT * FROM GetParamsFromTable()
ASKER
Is the above code to add to an asp page ? .. ASP/VB ? If it can only be run in sql analizer it wont work, this is to be added to an ASP page which should return a recordset, I am using Dreamweaver to do this.
create the functions by executing them in sql analyzer.
and use
SELECT * FROM GetParamsFromTable()
to get the data in ASP.
and use
SELECT * FROM GetParamsFromTable()
to get the data in ASP.
ASKER
I created the function, when I run in queryanalizer I get:
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
Which parameters do I need to pass to the function ?
A
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
Which parameters do I need to pass to the function ?
A
i think you tried
SELECT * FROM GetParamsFromTable
you need provide "()" without any params.
try
SELECT * FROM GetParamsFromTable()
SELECT * FROM GetParamsFromTable
you need provide "()" without any params.
try
SELECT * FROM GetParamsFromTable()
ASKER
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
did you change something in the funtion?
can you post the existing function script from your db?
can you post the existing function script from your db?
ASKER
First I modified this and ran:
create function GetParamsFromTable()
returns @params table(paramid int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select paramid, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin g](@paramN ame)
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
------
Then I tried to run :
ALTER FUNCTION [dbo].[GetListFromCSVStrin g]
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo s +1) )
end
Set @prevCurPos = @curPos+1
end
return
End
GO
But I get the following error:
Server: Msg 208, Level 16, State 6, Procedure GetListFromCSVString, Line 30
Invalid object name 'dbo.GetListFromCSVString' .
So i changed the name from getlistfromcsvstring to the GetParamsFromTable, but it doesnt work.
create function GetParamsFromTable()
returns @params table(paramid int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select paramid, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
------
Then I tried to run :
ALTER FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo
end
Set @prevCurPos = @curPos+1
end
return
End
GO
But I get the following error:
Server: Msg 208, Level 16, State 6, Procedure GetListFromCSVString, Line 30
Invalid object name 'dbo.GetListFromCSVString'
So i changed the name from getlistfromcsvstring to the GetParamsFromTable, but it doesnt work.
ok try as follows
STEP 1: run the following in query analyzer
drop function GetParamsFromTable
go
drop function GetListFromCSVString
STEP 2: run the following in query analyzer
create FUNCTION [dbo].[GetListFromCSVStrin g]
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo s +1) )
end
Set @prevCurPos = @curPos+1
end
return
End
GO
STEP 3: run the following in query analyzer
create function GetParamsFromTable()
returns @params table(id int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select id, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin g](@paramN ame)
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
finally try running
SELECT * FROM GetParamsFromTable()
STEP 1: run the following in query analyzer
drop function GetParamsFromTable
go
drop function GetListFromCSVString
STEP 2: run the following in query analyzer
create FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(500)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(';', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo
end
Set @prevCurPos = @curPos+1
end
return
End
GO
STEP 3: run the following in query analyzer
create function GetParamsFromTable()
returns @params table(id int,parameter varchar(500))
as
begin
declare cur_Main cursor
for Select id, ParamName from ParametersUsers
declare @id int, @paramName varchar(500)
open cur_Main
fetch next from cur_Main into @id, @paramName
while @@FETCH_STATUS=0
begin
insert into @params select @id, ListValue from [dbo].[GetListFromCSVStrin
fetch next from cur_Main into @id, @paramName
end
close cur_Main
deallocate cur_Main
return
end
GO
finally try running
SELECT * FROM GetParamsFromTable()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create function GetParamsFromTable() ...
should be
create function dbo.GetParamsFromTable() ...
and similary:
SELECT * FROM GetParamsFromTable()
needs to be:
SELECT * FROM dbo.GetParamsFromTable()
ms sql server is picky about the functions, the owner must be specified.
should be
create function dbo.GetParamsFromTable() ...
and similary:
SELECT * FROM GetParamsFromTable()
needs to be:
SELECT * FROM dbo.GetParamsFromTable()
ms sql server is picky about the functions, the owner must be specified.
create FUNCTION [dbo].[GetListFromCSVStrin
(
@csvString varchar(500)
)
RETURNS @ValueList TABLE
(
ListValue varchar(50)
)
AS
begin
declare @curPos int
declare @prevCurPos int
set @curPos = -1
set @prevCurPos = 1
While @curPos <>0
begin
set @curPos = charindex(',', @csvString, @curPos + 1)
if @curPos <> 0
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, @curPos-@prevCurPos) )
end
else
begin
insert into @ValueList values( substring(@csvString, @prevCurPos, len(@csvString)-@prevCurPo
end
Set @prevCurPos = @curPos+1
end
return
End
use it in your sql as follows
SELECT LISTITEMS.ListValue FROM dbo.ParametersUsers
outer apply [dbo].[GetListFromCSVStrin