Split function equivalent in T-SQL?

by

Try this

DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str="1,2,3,4,5,6,7,8,9,10,11,12,13,14,15"
SET @delimiter=","
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)

OR

DECLARE @str varchar(100), @delimiter varchar(10)
SET @str="1,2,3,4,5,6,7,8,9,10,11,12,13,14,15"
SET @delimiter=","
;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
    FROM CTE
    WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter) 
    THEN b - a - LEN(@delimiter) 
    ELSE LEN(@str) - a + 1 END) value      
FROM cte WHERE a > 0

Many more ways of doing the same is here How to split comma delimited string?

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Convert varchar into datetime in SQL Server
  7. Generate Dates between date ranges
  8. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  9. How to print VARCHAR(MAX) using Print Statement?
  10. Where value in column containing comma delimited values
  11. How to update Identity Column in SQL Server?
  12. SQL query to split column data into rows
  13. Common Table Expression, why semicolon?
  14. T-SQL split string based on delimiter
  15. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  16. Concatenate values based on ID
  17. Splitting delimited values in a SQL column into multiple rows
  18. SQL “between” not inclusive
  19. Can I set ignore_dup_key on for a primary key?
  20. Using PIVOT in SQL Server 2008
  21. Stored procedure that exports data into csv files only exports to one file
  22. How do I prevent a database trigger from recursing?
  23. TSQL – How to use GO inside of a BEGIN .. END block?
  24. How to combine results of two queries into a single dataset
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Executing SQL Server Agent Job from a stored procedure and returning job result
  27. SQL take just the numeric values from a varchar
  28. SQL Server SELECT INTO @variable?
  29. Using CASE Statement inside IN Clause
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment