?
Solved

Select to comma delimited

Posted on 2007-04-10
16
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 750 total points
ID: 18890101

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…
Suggested Courses

771 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