SQL Server Performance

how to get the data after the comma 2

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

  1. kushal New Member

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

    sorry i forgot to mention, i'm using MS SQL and PowerBuilder.
  3. FrankKalis Moderator

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

    thank Frank
    i'm getting the following error - 'Create Function' must be the fisrt statement in a query batch.
  5. FrankKalis Moderator

  6. kushal New Member

    sorry mate i don't no how can i check that.
  7. FrankKalis Moderator

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

    its ms sql 2000 version sql 8.00.194
  9. FrankKalis Moderator

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

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

    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
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  12. kushal New Member

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

    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(','+@strComma,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)
  14. kushal New Member

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

    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(','+@strComma,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
  16. Madhivanan Moderator

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

    its text
  18. Madhivanan Moderator

    Instead of fil_srt_cond use Convert(varchar(8000),fil_srt_cond) and try it again

    Madhivanan

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

    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'
  20. Madhivanan Moderator

    Try this

    SELECT @CSVString = Convert(varchar(8000),fil_srt_cond) from browse_style_sort_filter_list

    Madhivanan

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

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

    So do you want to remove semi-column (<img src='/community/emoticons/emotion-5.gif' alt=';)' /> from the result?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  23. kushal New Member

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

    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(','+@strComma,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
  25. kushal New Member

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

    When you concatenate the data add ; next to that number

    Madhivanan

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

    sorry, i'm not sure what you trying to ask me.
  28. kushal New Member

    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.




  29. Madhivanan Moderator

    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(','+@strComma,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
  30. kushal New Member

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

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

    Are you using SQL Server2000 or lower version?

    Madhivanan

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

    ms sq 2000, sql ver 8.00.194
  34. Madhivanan Moderator

    I dont know why You have square braces [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  35. kushal New Member

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

Share This Page