SQL Tip: HowTo Convert Delimited String to Table

Many times we need to deal with lists in T-SQL, for instance we need to have a list of values to pass it to IN clause in SQL. This list is supposed to look like delimited string. However we can not pass it to T-SQL procedure as a string because T-SQL compiler can not interpret delimited string as a list of values.

For MSSQL - the list of values is of table type, so we need to have a tool to convert delimited string to table. The function below does just that. So we are able to pass delimited strings to the procedure, convert this string to table and use it later in SQL IN close or in joins.

create function F_TBL_VALS_FROM_STRING
(@p_str varchar(1000))
returns @tbl table (VAL int)
as
begin
    set @p_str = @p_str + ','
    declare @p_counter int
    declare @p_len int
    declare @p_curr_char varchar(1)
    declare @p_char varchar(6)
    declare @p_num int
    set @p_len = len(@p_str) 
    set @p_counter = 1
    set @p_curr_char = ''
    set @p_char = ''

    -- loop
    while @p_counter <= @p_len 
    begin
        set @p_curr_char = substring(@p_str, @p_counter, 1)
        if (@p_curr_char <> ',')
        begin
            set @p_char = @p_char + @p_curr_char         
        end
        else
        begin  
            set @p_num = convert(int, @p_char)         
            insert into @tbl values(@p_num)
            set @p_char = ''
        end
       set @p_counter = @p_counter + 1
    end
    return
end

Some raw T-SQL for your consideration...

Related Posts: