Solved

Select to comma delimited

Posted on 2007-04-10
16
232 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

773 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