Solved

Select to comma delimited

Posted on 2007-04-10
16
230 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article describes some very basic things about SQL Server filegroups.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

930 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

12 Experts available now in Live!

Get 1:1 Help Now