Split Function by Comma in SQL Server 2008

Split function by comma in SQL Server 2008

I've change the function name so it won't overlapped in what the Split() function really does.

Here is the code:

CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as     
begin

declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return null

declare @ColCnt int
    set @ColCnt = 1

while (@idx != 0)
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0 begin
        if (@ColCnt = @Column) return left(@String,@idx - 1)        

        set @ColCnt = @ColCnt + 1

    end

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break
end 
return @String  
end

And here is the usage:

select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)

Split string by comma in SQL Server 2008

Following will work -

DECLARE @A VARCHAR (100)= '4,5,6'
DECLARE @B VARCHAR (100)= 'RXXXXXX'
DECLARE @RETURN_VALUE BIT = 1 --DEFAULT 1

SELECT items
INTO #STRINGS 
FROM dbo.split(@A,',')

IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CHARINDEX(items, @B) > 0)
SET @RETURN_VALUE = 0

PRINT @RETURN_VALUE

DROP TABLE #STRINGS

You can also use CONTAINS instead of CHARINDEX -

IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CONTAINS(items, @B))
SET @RETURN_VALUE = 0

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

How to separate (split) string with comma in SQL Server stored procedure

If you pass the comma separated (any separator) string to store procedure and use in query so must need to spit that string and then you will use it.

Below have example:

DECLARE @str VARCHAR(500) = 'monday,tuesday,thursday'
CREATE TABLE #Temp (tDay VARCHAR(100))
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
    IF CHARINDEX(',',@str) > 0
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
    ELSE
        BEGIN
        SET  @TDay = @str
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END

 SELECT * 
 FROM tblx 
 WHERE days IN (SELECT tDay FROM #Temp)

Comma separted string into columns using SQL Server 2008 R2

First you need to create a table valued function which will return the comma separated values in the form of a table.

Implement the below code for the function, which I found here, with a slight modification to return an identity column based on which you will later do a JOIN to get a tuple of the 1st value for column A and the first value for column B (A1, Z1) and so on the 2nd, 3rd etc. :

CREATE FUNCTION Split (@InputString VARCHAR(8000), @Delimiter VARCHAR(50))

RETURNS @Items TABLE (ID INTEGER IDENTITY(1,1), Item VARCHAR(8000))

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item VARCHAR(8000)
      DECLARE @ItemList VARCHAR(8000)
      DECLARE @DelimIndex INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

---- Set Permissions
--GRANT SELECT ON Split TO UserRole1
--GRANT SELECT ON Split TO UserRole2
--GO

Now, after creating this function, modify your stored procedure to:

CREATE PROCEDURE m1_test @stringCola VARCHAR(max), @stringColb VARCHAR(max)
AS
INSERT INTO m1
SELECT A.Item, B.Item
FROM Split(@stringColA, ',') A
    INNER JOIN Split(@stringColB, ',') B ON A.ID = B.ID
GO

---

Related Topics