how to put a split function into query select sql

Question

I want to put a split function into select like

CREATE TABLE #tmp(id INT ,codes VARCHAR(10))


INSERT INTO #tmp(id,codes) VALUES (1,'1;2;3')


SELECT id , BaseSplit(codes,';')  FROM #tmp 

but how ???

Answer 1

You need to use CROSS APPLY something like this....

SELECT  * FROM #tmp
CROSS APPLY dbo.BaseSplit(#tmp.codes, ';') AS htvf

Answer 2

I don't believe your proposed query will work because you're trying to return a record set as part of the select. This will throw a Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. If you want to split that value up as a table, you can use a UDF.

Here's a slightly modified UDF that was originally posted by Dave Tucker here back in 2005.

CREATE FUNCTION dbo.Split(@String NVARCHAR(MAX), @Delimiter NVARCHAR(MAX))
RETURNS @Results TABLE (Items NVARCHAR(MAX))
AS
BEGIN
    DECLARE @index INT
    DECLARE @slice NVARCHAR(MAX)

    SELECT @index = 1
    IF @String IS NULL RETURN

    WHILE @index != 0

    BEGIN
        SELECT @index = CHARINDEX(@Delimiter,@String)
        IF @index !=0
        SELECT @slice = LEFT(@String,@index - LEN(@Delimiter))
        ELSE
        SELECT @slice = @String

        INSERT INTO @Results(Items) VALUES(@slice)
        SELECT @String = RIGHT(@String,LEN(@String) - @index)
        IF LEN(@String) = 0 BREAK
    END RETURN
END

USAGE:

SELECT * FROM dbo.Split('1;2;3', ';')