SQL Server Performance

Parse Delimited String -tsql help

Discussion in 'SQL Server 2005 General Developer Questions' started by sql_jr, Feb 5, 2007.

  1. sql_jr New Member

    Hi, I wanted to be able to parse a string in a column that is delimited by a '' character. Didn't want too much bulk code, and not sure if this is possible in one statement. I've tried CHARINDEX, but maybe there are easier or better ways?

    It should have this logic:
    say string = MyGenreMyClassMySuBCategory

    select X as Position1, Y as Position2, Z as Position3

    Postion1 Position2 Position3
    --------------------------------
    My Genre MyClass MySuBCategory

    Thx for help in advance!
  2. MohammedU New Member

    Use CHARINDEX with SUBSTRING function to get the desired output..

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. khtan New Member

  4. sql_jr New Member

    Thanks khtan, that's exactly what I'm looking for - although I'd like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value). Can you or anyone give some help on that?
    I'd like to see:

    DValue1,DValue2,DValue3,Field1, Field2, (etc....)

    The function is below.

    CREATE FUNCTION dbo.Split
    (
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
    )
    RETURNS @RtnValue table
    (
    Id int identity(1,1),
    Data nvarchar(100)
    )
    AS
    BEGIN
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
    Insert Into @RtnValue (data)
    Select
    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
    END
  5. khtan New Member

    "although I'd like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value)"

    What do you mean by this ?


    KH
  6. MohammedU New Member

    See if it works for you<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />declare @sql Varchar(1000)<br />select @sql = 'MyGenreMyClassMySuBCategory'<br />select @sql = 'select '+char(39)+replace(@sql, '', ''',''')+char(39)<br />exec( @sql)<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
  7. sql_jr New Member

    Thx, MohammedU - this may work, but can you tell me how to incorporate this into a more complex sql query.
    What I mean is rather than {select @sql = 'MyGenreMyClassMySuBCategory'} I want to substiture the column name of the table to parse, rather than give it a static value. Will this work?

    Edit: Actually I tried this as test without the dynamic sql part, and it doesn't give desired result. Although it replaces it into comma delimited, I wanted to represent each part as a column. Thx
  8. FrankKalis Moderator

  9. sql_jr New Member

    Ok, ALL - one more bit of help needed:

    I've chosen this function below, which suits me fine:
    However I'd like a way to modify it so I can return a null or empty string if the fieldnum I pass in doesn't exist. As it currently stands, it returns an invalid parameter passed error. Some data in the field has 'ac' and others have 'a' only, so if I try to specify field3 when only 'a' exists, I get the error. Any ideas?



    http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html
    ----------------------------------------------------------------
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetEntryDelimiitted]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_GetEntryDelimiitted]
    GO

    Create function f_GetEntryDelimiitted
    (
    @line varchar(4000) ,
    @fldnum int ,
    @delim varchar(10) ,
    @quoted varchar(1)-- Y/N
    )
    returns varchar(400)
    as
    begin
    /*
    create table #a (s varchar(1000))
    insert #a select '"John","Smith","IT",2,"sql",1'
    insert #a select '"Fred","Jones","Assassin",7,"Guns",5'
    insert #a select '"Peter","Brown","Politics",23,minister,5'

    select a.* from
    (
    select Forname= dbo.f_GetEntryDelimiitted (s, 1, ',', 'Y') ,
    Surname = dbo.f_GetEntryDelimiitted (s, 2, ',', 'Y') ,
    Profession = dbo.f_GetEntryDelimiitted (s, 3, ',', 'Y') ,
    ProfessionExp = dbo.f_GetEntryDelimiitted (s, 4, ',', 'Y') ,
    Skill = dbo.f_GetEntryDelimiitted (s, 5, ',', 'Y') ,
    SkillExp = dbo.f_GetEntryDelimiitted (s, 6, ',', 'Y')
    from #a
    ) a

    drop table #a
    */

    declare@i int ,
    @j int ,
    @delimUsed varchar(11) ,
    @s varchar(4000)

    select @i = 1
    while@fldnum > 0
    begin
    select @fldnum = @fldnum - 1
    if substring(@line, @i, 1) = '"' and @Quoted = 'Y'
    begin

    select @delimUsed = '"' + @Delim ,
    @i = @i + 1
    end
    else
    begin
    select @delimUsed = @Delim
    end

    select @j = charindex(@delimUsed, @line, @i)
    if @j = 0
    select @j = datalength(@line) + 1
    if @fldnum > 0
    select @i = @j +len(@delimused)
    end

    select @s = substring(@line, @i, @j - @i)
    return@s
    end

    go
  10. khtan New Member

    Oh, Nigel's function.

    You can change



    select @s = substring(@line, @i, @j - @i)

    to


    select @s = case when @j > @i then substring(@line, @i, @j - @i)
    else NULL
    end



    KH

Share This Page