how to get the data after the comma | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to get the data after the comma

hi there
i have a field name(fil_srt_cond) with the values of cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3 i used the char index:- substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord, substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len, substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str
to display them in their specific fields like NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC however for cmpnt_stuff,DESC,2,3 i’m getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES therefore i’m not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance
>>cmpnt_name,ASC,1,2 Do you want to split it and show as cmpnt_name ASC 1 2 ?
Madhivanan Failing to plan is Planning to fail
thank you for helping me yes i want to split them so i can extract the data then display in a report.
FIELD – Tag
Order- Asc
Length – 2
Start_Char – 1
Hi,
here is a example of parsing string function : http://www.mindsdoor.net/SQLTsql/ParseCSVString.html
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

well i checked the link, it looks complicated also i’m new to programming. is there another way to get the data. thanks
Execute the code described above then
Run this
Select * from fn_ParseCSVString(‘cmpnt_name,ASC,1,2′,’,’) Madhivanan Failing to plan is Planning to fail
i’ using powerbuilder(report) and ms sql, i tried but it says error ‘GO’. sorry
You should run that Script in the Query Analyser Madhivanan Failing to plan is Planning to fail
hi there i really apreciated your help, however i’m not allow to run script in the ms sql. lets recap here is the situation,
i need to get the data data a – 1 & 2
data b – 2 & 3 table Comp – Name
Order – ASC
Char – 1
length – 2 Comp – Suffix
Order – DESC
Char – 2
length – 3 cmpnt_name,ASC,1,2;cmpnt_suff,DESC,2,3;…………….. i used this command to obtain the order:- substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord, however because the order can be 3 char (ASC) or 4 char (DESC), i cann’t get the second set of data correctly, i getting ‘,’instead.

>>however i’m not allow to run script in the ms sql. Why? Are you using Crystal Reports?
Use split function there Madhivanan Failing to plan is Planning to fail
security things work policy blabla thats from the manager.
i’m using powerbuilder to create reports.
Isnt there any split function in PowerBuilder? Madhivanan Failing to plan is Planning to fail
there’s no split function. and i’m using ms sql. any ideas how to use a simple sql query to get the data out please
Run this DECLARE @CSVString varchar(8000), @Delimiter varchar(10)
SELECT @CSVString = ‘cmpnt_name,ASC,1,2’
Declare @tbl table(st varchar(300))
select @Delimiter=’,’
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
select * from @tbl
Madhivanan Failing to plan is Planning to fail
hi there its working ok
but how to include my database table and i need to replace ‘cmpnt_name,ASC,1,2’ to my field name which is flt_str_cond from the table browse_style[/b so i can get all the data from the database. also how to seperate the one field into 4 fields such as name, order, star_char and length.
Instead of SELECT @CSVString = ‘cmpnt_name,ASC,1,2’
use SELECT @CSVString = flt_str_cond from yourTable Post the table structure Madhivanan Failing to plan is Planning to fail
Browsw_style_sort_filter TABLE
FIL STR ID – 62242
BROWSE ID – 62245
FIL STR FLG –
FIL STR TYP –
FIL STR NAME –
FIL STR DESC –
FIL STR COND- – CMPNT-NAME,ASC,10,2;CMPNT_STUFF,DESC,2,3;CMPNT_FUNC_TYPE_ID,ASC, 1,4;
.
.
.
.
i’m getting strange values such as mod_id, like, 33328,1; when i added the fil_str_cond and the table name.
also how can i display the list according to their specific fiels.
thx
Only solution is you need to use as suggested by the link
Why cannot you create a function to return this? This will work for only one row DECLARE @CSVString varchar(8000), @Delimiter varchar(10)
SELECT @CSVString = FIL STR COND from Browsw_style_sort_filter
Declare @tbl table(st varchar(300))
select @Delimiter=’,’
declare @i int ,
@j int
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
select * from @tbl
Madhivanan Failing to plan is Planning to fail
thx for your help
i going to have a serious chat with the manager and i’ll let you know 2morrow (australia) if i can use the script you gave me before
thank you very much for your help, i learnt alot.
i have managed to the data. my other problem is say the length1 is 10, length2 is 3 how can i get the number of digits in the length such as 10 has 2 digits and 3 has 1 digit so i can set the number char in the substring variable instead of 2 see below. substring(BSSF.fil_srt_cond,charindex(BS.column_name,BSSF.fil_srt_cond) + LEN(BS.column_order) +LEN(BS.column_name)+4,2) length,
thank you.
Post sample data and the result you want Madhivanan Failing to plan is Planning to fail
do you need this declare @string varchar(50)
select @string=’abcd’
select len(convert(varchar(10),len(@string)))
i have increase the length1 of the tag to 10. therefore the fil_str_cond field is as follows:- cmpnt_name,ASC,10,2;cmpnt_suff,DESC,2,3;cmpnt_func_type_id,ASC,1,4; in the table it as follows
______________________________________________
Field*******Length*******Str Char********Order Tag Name—- 10————- , ———–ASC
____________________________________________ Suffix——2, ————- 3 ———–DESC
____________________________________________ Instrument- 1, ————- 4————ASC as you can see in the tag name row there’s a comma in the star char column it should be 2
in suffix’ 2,’ should be only 2
in instrument’ 1,’ should only b 1
Post the table structure with datatype and column widths and the query you used Madhivanan Failing to plan is Planning to fail
hi rangitjain here is my query – i’m a bit confused – how can i implement ur code into it SELECT BSSF.fil_srt_cond,
BS.column_heading, substring(BSSF.fil_srt_cond,charindex(BS.column_name,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord, substring(BSSF.fil_srt_cond,charindex(BS.column_name,BSSF.fil_srt_cond) + LEN(BS.column_order) +LEN
(BS.column_name+4,2)leng , substring(BSSF.fil_srt_cond,charindex(BS.column_name,BSSF.fil_srt_cond) + LEN(BS.column_order) +LEN(BS.column_name)+6,1)
str_char FROM BROWSE_STYLE_SORT_FILTER_LIST BSSF,
BROWSE_STYLE_COLUMN BS,
BROWSE_VIEW BV
where BSSF.fil_srt_id = 62243 AND
BV.SORT_ID = BSSF.fil_srt_id and
BV.style_id = BS.style_id and
BS.column_visible = 1 and
charindex(BS.column_name,BSSF.fil_srt_cond) <> ‘0’
Hi Kushal,
But is it solving you need did you wanted the same.
Then what is confusion in applying it.
you simply change the variable @string with the string for which you are calculating length.
declare @string varchar(50)
–select @string=’abcd’
select len(convert(varchar(10),len(@string)))

]]>