SQL Server Performance Forum – Threads Archive

how to get the data after the comma 2

hi there
i’m following what Madhivanan and Ghemant in the ‘how to get the data after the comma’ topic.
so its as follows if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_ParseCSVString]’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function [dbo].[fn_ParseCSVString]
GO
create function fn_ParseCSVString
(
@CSVString varchar(8000) ,
@Delimitervarchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString (‘qwe,c,rew,c,wer’, ‘,c,’)
*/
begin
declare @i int ,
@j int
select @i = 1
while @i <= len(@CSVString)
begin
[email protected] = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
[email protected] = len(@CSVString) + 1
end
[email protected] select substring(@CSVString, @i, @j – @i)
[email protected] = @j + len(@Delimiter)
end
return
end GO
Select * from fn_ParseCSVString(‘cmpnt_name,ASC,1,2′,’,’) however i’m getting the error – incorrect syntax near ‘go’. Also how can i replace (‘cmpnt_name,ASC,1,2′,’,’) so that i can get the data from column fil_str_cond in the browse_style_sort_filter_list table. thank you once again

sorry i forgot to mention, i’m using MS SQL and PowerBuilder.

This is working for me when working in Query Analyzer. Anyway, to use a table column you might rewrite this to something like
CREATE TABLE browse_style_sort_filter_list
(
fil_str_cond VARCHAR(8000)
)
INSERT INTO browse_style_sort_filter_list SELECT ‘cmpnt_name,ASC,1,2’
GO create function fn_ParseCSVString
(@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
DECLARE @CSVString VARCHAR(8000)
declare @i int , @j int
SELECT @CSVString = fil_str_cond FROM browse_style_sort_filter_list
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j – @i)
select @i = @j + len(@Delimiter)
end
return
end GO
Select * from fn_ParseCSVString(‘,’)
DROP FUNCTION fn_ParseCSVString
DROP TABLE browse_style_sort_filter_list However, you might also want to read these very informative articles:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/arrays-in-sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

thank Frank
i’m getting the following error – ‘Create Function’ must be the fisrt statement in a query batch.

Are you on SQL Server 2000 with compatiblity level 80? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

sorry mate i don’t no how can i check that.

For example: sp_helpdb ‘your db name’
Just asking, because with SQL Server 7.0 or compatibility level less than 80 you can’t use a UDF. Just to make sure, We can rule out this.
Otherwise I think you need to post your complete statement you’re trying to execute. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

its ms sql 2000 version sql 8.00.194

This is not exactly the same. You can have a SQL Server 2000 database running in 7.0 mode in which you are unable to use UDF’s. Anyway, what was the exact statement you’ve tried to execute? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

CREATE TABLE temp (fil_str_cond VARCHAR(8000))
INSERT INTO temp SELECT BROWSE_STYLE_SORT_FILTER_LIST.fil_srt_cond FROM browse_STYLE_SORT_FILTER_LIST
GO create function fn_ParseCSVString
(@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
DECLARE @CSVString VARCHAR(8000)
declare @i int , @j int
SELECT @CSVString = fil_str_cond FROM temp
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j – @i)
select @i = @j + len(@Delimiter)
end
return
end GO
Select * from fn_ParseCSVString(‘,’)
DROP FUNCTION fn_ParseCSVString
DROP TABLE temp

Hi,
okay run sp_dbcmptlevel from QA and post the output here , or verify if it has 80 in it or not . Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

i don’t have access to the QA and just been informed that we can’t use create function in our queries. sorry guys

No problem. Check out the links I’ve posted. There are also methods available that don’t require a UDF. Even a single SELECT statement like this is possible:
DECLARE @strComma VARCHAR(1000)
SET @strComma = ‘cmpnt_name,ASC,1,2’ SELECT
CAST(RIGHT(LEFT(@strComma,Number-1)
, CHARINDEX(‘,’,REVERSE(LEFT(‘,’[email protected],Number-1)))) AS CHAR(30))
FROM
master..spt_values
WHERE
Type = ‘P’ AND Number BETWEEN 1 AND LEN(@strComma)+1
AND
(SUBSTRING(@strComma,Number,1) = ‘,’ OR SUBSTRING(@strComma,Number,1) = ”)
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

hey Frank i used ur code above and it works, however i’m getting the data in one field, how can i get the data in their specific fields as i need to display them in a report like below. Name———-Char———-Length———-order
Tag Number—–1————–2————-ASC thx u

hi guys i’m getting this error message:- ‘the text, ntext, and image data types are invalid’. i think its the ‘SELECT @CSVString = (select fil_srt_cond from BROWSE_STYLE_SORT_FILTER_LIST’ line. it was SET @strComma = ‘cmpnt_name,ASC,1,2’ before.
DECLARE @strComma VARCHAR(1000)
SELECT @CSVString = (select fil_srt_cond from BROWSE_STYLE_SORT_FILTER_LIST)
SELECT CAST(RIGHT(LEFT(@strComma,Number-1) , CHARINDEX(‘,’,REVERSE(LEFT(‘,’[email protected],Number-1)))) AS CHAR(30))FROM master..spt_valuesWHERE Type = ‘P’ AND Number BETWEEN 1 AND LEN(@strComma)+1 AND (SUBSTRING(@strComma,Number,1) = ‘,’ OR SUBSTRING(@strComma,Number,1) = ”)
thank you in advance

You cannt use Left, Right functions at the Columns having Data Types Text, Ntext until you convert it to varchar What is the datatype of that column? Madhivanan Failing to plan is Planning to fail

its text

Instead of fil_srt_cond use Convert(varchar(8000),fil_srt_cond) and try it again Madhivanan Failing to plan is Planning to fail

SELECT @CSVString = select (Convert(varchar(8000),fil_srt_cond)) from browse_style_sort_filter_list is giving me this error:- incorrect syntax near the keyword ‘select’

Try this SELECT @CSVString = Convert(varchar(8000),fil_srt_cond) from browse_style_sort_filter_list Madhivanan Failing to plan is Planning to fail

yeah is working SELECT @CSVString = Convert(varchar(8000),fil_srt_cond) from browse_style_sort_filter_list however i’m getting these values cmpnt_name ASC 10 2;cmpnt_stuff DESC 2 3;cmpnt_func_type_id ASC 1 4;cmpnt_serv ASC 25 8; the fil_srt_cond field has the value of cmpnt_name,ASC,10,2;cmpnt_stuff,DESC,2,3;cmpnt_func_type_id,ASC,1,4;cmpnt_serv,ASC,25,8; i think the code is not looking for the semi-column (<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />

So do you want to remove semi-column (<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> from the result? Madhivanan Failing to plan is Planning to fail

hi yeah i want to remove the semi-column so i can get these results. cmpnt_name
ASC
10
2 cmpnt_stuff
DESC
2
3 cmpnt_func_type_id
ASC
1
4 cmpnt_serv
ASC
25
8

Just replace ; by ” DECLARE @strComma VARCHAR(1000)
SET @strComma = ‘cmpnt_name,ASC,10,2;cmpnt_stuff,DESC,2,3;cmpnt_func_type_id,ASC,1,4;cmpnt_serv,ASC,25,8;’
set @strComma=replace(@strComma,’;’,”)
SELECT
CAST(RIGHT(LEFT(@strComma,Number-1)
, CHARINDEX(‘,’,REVERSE(LEFT(‘,’[email protected],Number-1)))) AS CHAR(30))
FROM
master..spt_values
WHERE
Type = ‘P’ AND Number BETWEEN 1 AND LEN(@strComma)+1
AND
(SUBSTRING(@strComma,Number,1) = ‘,’ OR SUBSTRING(@strComma,Number,1) = ”)
Madhivanan Failing to plan is Planning to fail

it seems working…
however the data is as follows cmpnt_name
ASC
10
2cmpnt_stuff
DESC
2
3cmpnt_func_type_id
ASC
1
4cmpnt_serv
ASC
25
8 it would be fantastic if u can seperate them:-
2cmpnt_stuff
3cmpnt_func_type_id
4cmpnt_serv to make it as follows
cmpnt_name
ASC
10
2 cmpnt_stuff
DESC
2
3 cmpnt_func_type_id
ASC
1
4 cmpnt_serv
ASC
25
8

When you concatenate the data add ; next to that number Madhivanan Failing to plan is Planning to fail

sorry, i’m not sure what you trying to ask me.

well i would like the data format to be like below, that is in their specific column. Name———-Char———-Length———-order Tag Number—–1————–2————-ASC Suffix———2————–3————-DESC Instrument—–1————–4————–ASC just like a report with cloumns and rows.

In Query Analyser set Results in Text (Press Ctrl+T) and run this DECLARE @strComma VARCHAR(1000)
SET @strComma = ‘cmpnt_name,ASC,10,2cmpnt_stuff,DESC,2,3cmpnt_func_type_id,ASC,1,4cmpnt_serv,ASC,25,8;’
set @strComma=replace(@strComma,’;’,”)
Declare @t table(data varchar(200)) Insert into @t
SELECT
CAST(RIGHT(LEFT(@strComma,Number-1)
, CHARINDEX(‘,’,REVERSE(LEFT(‘,’[email protected],Number-1)))) AS CHAR(30))
FROM
master..spt_values
WHERE
Type = ‘P’ AND Number BETWEEN 1 AND LEN(@strComma)+1
AND
(SUBSTRING(@strComma,Number,1) = ‘,’ OR SUBSTRING(@strComma,Number,1) = ”) Select case when data like ‘[0-9][a-Z]%’ then left(data,1) +char(10)+ char(13)+substring(data,2,len(data))
else data end from @t Madhivanan Failing to plan is Planning to fail

here are the results cmpnt_name ASC 10 2#8300;#8300;cmpnt_stuff DESC 2 3#8300;#8300;cmpnt_func_type_id ASC 1 4#8300;#8300;cmpnt_serv ASC 25 8

here are the results
[][] they are squares
cmpnt_name ASC 10 2[][]cmpnt_stuff DESC 2 3[][]cmpnt_func_type_id ASC 1 4[][]cmpnt_serv ASC 25 8

Are you using SQL Server2000 or lower version? Madhivanan Failing to plan is Planning to fail

ms sq 2000, sql ver 8.00.194

I dont know why You have square braces [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

its not square brackets but squares. i represented it as square brackets.

]]>