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;FontItalic=0;FontUnderline=0;TextColor=0,0,0;BackColor=255,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
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
create a function as follows

create FUNCTION [dbo].[GetListFromCSVString]
(            
      @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)-@prevCurPos +1) )
            end
            Set @prevCurPos = @curPos+1
      end
      return

End


use it in your sql as follows


SELECT LISTITEMS.ListValue  FROM dbo.ParametersUsers
outer apply [dbo].[GetListFromCSVString](ParamName) as LISTITEMS
0
appariCommented:
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].[GetListFromCSVString](ParamName) as LISTITEMS
where LISTITEMS.ListValue like 'FontItalic=%'
0
AleksAuthor Commented:
I tried the second solution got this error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'outer'.

A
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

appariCommented:
what is your sql server version? outer apply works in 2005 only
0
AleksAuthor Commented:
MS SQL 2000
0
appariCommented:
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].[GetListFromCSVString](@paramName)
            fetch  next from cur_Main into @id, @paramName
      end
      close cur_Main
      deallocate cur_Main
      return
end

GO


ALTER FUNCTION [dbo].[GetListFromCSVString]
(            
      @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)-@prevCurPos +1) )
            end
            Set @prevCurPos = @curPos+1
      end
      return

End
GO




and execute the following sql

SELECT *  FROM GetParamsFromTable()
0
AleksAuthor Commented:
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.
0
appariCommented:
create the functions by executing them in sql analyzer.

and use
SELECT *  FROM GetParamsFromTable()

to get the data in ASP.
0
AleksAuthor Commented:
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
0
appariCommented:
i think you tried
SELECT *  FROM GetParamsFromTable

you need provide "()" without any params.
try
SELECT *  FROM GetParamsFromTable()
0
AleksAuthor Commented:
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
0
appariCommented:
did you change something in the funtion?
can you post the existing function script from your db?
0
AleksAuthor Commented:
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].[GetListFromCSVString](@paramName)
            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].[GetListFromCSVString]
(            
      @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)-@prevCurPos +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.

0
appariCommented:
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].[GetListFromCSVString]
(            
      @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)-@prevCurPos +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].[GetListFromCSVString](@paramName)
            fetch  next from cur_Main into @id, @paramName
      end
      close cur_Main
      deallocate cur_Main
      return
end

GO



finally try running

SELECT *  FROM GetParamsFromTable()

0
appariCommented:

in STEP 1 if you get error just ignore it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.