SQL Server Performance

Split string with delimited chars into a table

Discussion in 'Contribute Your SQL Server Scripts' started by rajeshjacob, Sep 11, 2006.

  1. rajeshjacob New Member

    /* By Rajesh Jacob(Database Administrator-SAIC)

    ---this functuion helps us to split the delimited string value into a table which is not directly possible in sql server 2000 and previous versions.

    EG:- SELECT * FROM [dbo].[dbo].nom_return_splited_val_fn('text,to,be,splited',',')

    */


    create FUNCTION [dbo].nom_return_splited_val_fn(@l_state AS VARCHAR(8000),
    @l_delimiter char(1))
    RETURNS @state_t TABLE(seq_no numeric(9),
    state_split varchar(8000)
    )
    as
    BEGIN
    DECLARE @l_state_len numeric(9)
    DECLARE @l_state_plit varchar(8000)
    DECLARE @l_state_remain varchar(8000)
    DECLARE @l_counter numeric(9)
    SET @l_state_len=LEN(LTRIM(RTRIM(@l_state)))
    SET @l_state_remain=@l_state
    set @l_counter=1
    WHILE @l_state_len>0
    BEGIN
    if CHARINDEX(@l_delimiter,@l_state_remain,0)>0
    begin
    SET @l_state_plit =LEFT(@l_state_remain,CHARINDEX(@l_delimiter,@l_state_remain,0)-1)

    INSERT INTO @state_t values(@l_counter,@l_state_plit)

    SET @l_state_remain= RIGHT(@l_state_remain,len(@l_state_remain)-CHARINDEX(@l_delimiter,@l_state_remain,0))

    SET @l_state_len=LEN(LTRIM(RTRIM(@l_state_remain)))
    set @l_counter=@l_counter+1
    end
    else
    begin

    INSERT INTO @state_t values(@l_counter,@l_state_remain)
    SET @l_state_len=0
    end
    END
    if not EXISTS(select * from @state_t) --if null string is passed an empty record is added
    begin
    insert into @state_t
    values(1,'')
    end
    RETURN
    END
  2. Madhivanan Moderator

    Good
    If front ends like vb, asp,etc are used, then splitting should be done there which is very easier than doing in sql

    Madhivanan

    Failing to plan is Planning to fail
  3. veereshnashi New Member

    Looks Good. Well the same script I have already uploaded in the form,
    Check the for the topic "Comma Seperated values to table form ", Madhivanan any comments ?

    Thanks and regards,
    Veeresh.


    You Store, I Manage
  4. Madhivanan Moderator

    quote:Originally posted by veereshnashi

    Looks Good. Well the same script I have already uploaded in the form,
    Check the for the topic "Comma Seperated values to table form ", Madhivanan any comments ?

    Thanks and regards,
    Veeresh.


    You Store, I Manage
    Which form did you load?
    Can you give me more informations?

    Madhivanan

    Failing to plan is Planning to fail

Share This Page