T-SQL script or function that will get selected values from a comma delimited string

Question

if i have a string with values like A,B,C,D,E,F,

and i wanted to remove the last comma? is there a function or script i can use to do that?

Thanks

Answer 1

The below example will trim off the last char of a string but wont care if it's a comma or not

DECLARE @s VARCHAR(50)
SET @s = 'a,b,'

SELECT LEFT(@s, LEN(@s)-1)

Answer 2

If I'm understanding you additional question correctly, I believe you want to archive something like the below...

DECLARE @b TABLE
    (
        [stuff] VARCHAR(2)
    )

INSERT INTO @b VALUES('c')

DECLARE @s VARCHAR(MAX)
SET @s = 'a,b,c'

SELECT [stuff]
FROM @b b
INNER JOIN dbo.list(@s) lst
    ON lst.val = b.[stuff]

CREATE function [dbo].[list] ( @list VARCHAR(MAX) )
RETURNS @list_table TABLE ([val] VARCHAR(20))
AS
BEGIN
    DECLARE @index INT,
            @start_index INT,
            @val VARCHAR(20)

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN

            SELECT @val = SUBSTRING(@list, @start_index, @index - @start_index )
            INSERT @list_table ([val]) VALUES (@val)
            SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @val = SUBSTRING(@list, @start_index, @index - @start_index )
    INSERT @list_table ([val]) VALUES (@val)
    RETURN
END

The function just splits up the input string into rows in a table with a column "val" - this means you can then pass in a comma delimited string and use it to filter another table.