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

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

well i checked the link, it looks complicated also i'm new to programming. is there another way to get the data. thanks

i' using powerbuilder(report) and ms sql, i tried but it says error 'GO'. sorry

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.

security things work policy blabla thats from the manager.
i'm using powerbuilder to create reports.

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

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.

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

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.

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

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)))