SQL Server Performance

how to get the data after the comma

Discussion in 'General DBA Questions' started by kushal, Oct 5, 2005.

  1. kushal New Member

    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

  2. Madhivanan Moderator

    >>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
  3. kushal New Member

    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
  4. ghemant Moderator

  5. kushal New Member

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

    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
  7. kushal New Member

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

    You should run that Script in the Query Analyser

    Madhivanan

    Failing to plan is Planning to fail
  9. kushal New Member

    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.


  10. Madhivanan Moderator

    >>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
  11. kushal New Member

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

    Isnt there any split function in PowerBuilder?

    Madhivanan

    Failing to plan is Planning to fail
  13. kushal New Member

    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
  14. Madhivanan Moderator

    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
    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
  15. kushal New Member

    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.
  16. Madhivanan Moderator

    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
  17. kushal New Member

    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;
    .
    .
    .
    .

  18. kushal New Member

    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
  19. Madhivanan Moderator

    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
  20. kushal New Member

    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.
  21. kushal New Member

    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.
  22. Madhivanan Moderator

    Post sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  23. ranjitjain New Member

    do you need this

    declare @string varchar(50)
    select @string='abcd'
    select len(convert(varchar(10),len(@string)))

  24. kushal New Member

    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
  25. Madhivanan Moderator

    Post the table structure with datatype and column widths and the query you used

    Madhivanan

    Failing to plan is Planning to fail
  26. kushal New Member

    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'
  27. ranjitjain New Member

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

Share This Page