Solved

Select to comma delimited

Posted on 2007-04-10
16
225 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:amucinobluedot
  • 9
  • 6
16 Comments
 
LVL 39

Expert Comment

by:appari
ID: 18886541
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
 
LVL 39

Expert Comment

by:appari
ID: 18886552
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
 

Author Comment

by:amucinobluedot
ID: 18887193
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
 
LVL 39

Expert Comment

by:appari
ID: 18887333
what is your sql server version? outer apply works in 2005 only
0
 

Author Comment

by:amucinobluedot
ID: 18889154
MS SQL 2000
0
 
LVL 39

Expert Comment

by:appari
ID: 18889578
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
 

Author Comment

by:amucinobluedot
ID: 18889637
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
 
LVL 39

Expert Comment

by:appari
ID: 18889658
create the functions by executing them in sql analyzer.

and use
SELECT *  FROM GetParamsFromTable()

to get the data in ASP.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:amucinobluedot
ID: 18889763
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
 
LVL 39

Expert Comment

by:appari
ID: 18889830
i think you tried
SELECT *  FROM GetParamsFromTable

you need provide "()" without any params.
try
SELECT *  FROM GetParamsFromTable()
0
 

Author Comment

by:amucinobluedot
ID: 18889864
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetParamsFromTable.
0
 
LVL 39

Expert Comment

by:appari
ID: 18889918
did you change something in the funtion?
can you post the existing function script from your db?
0
 

Author Comment

by:amucinobluedot
ID: 18890039
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
 
LVL 39

Expert Comment

by:appari
ID: 18890092
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
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 18890101

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20470266
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now