score:0
I know this is an older post but thought I'd add an update. Tally Table and cteTally table based splitters all have a major problem. They use concatenated delimiters and that kills their speed when the elements get wider and the strings get longer.
I've fixed that problem and wrote an article about it which may be found at he following URL. http://www.sqlservercentral.com/articles/Tally+Table/72993/
I'll also tell you that a fellow by the name of "Peter" made an improvement even to that code (in the discussion for the article). The article is still interesting and I'll be updating the attachments with Peter's enhancements in the next day or two. Between my major enhancement and the tweek Peter made, I don't believe you'll find a faster T-SQL-Only solution for splitting VARCHAR(8000). I've also solved the problem for this breed of splitters for VARCHAR(MAX) and am in the process of writing an article for that, as well.
score:3
You can write a table function, and join your column to it with CROSS APPLY
. Here's my version.
CREATE FUNCTION dbo.Splitter(@text nvarchar(max), @separator nvarchar(100))
RETURNS @result TABLE (i int, value nvarchar(max))
AS
BEGIN
DECLARE @i int
DECLARE @offset int
SET @i = 0
WHILE @text IS NOT NULL
BEGIN
SET @i = @i + 1
SET @offset = charindex(@separator, @text)
INSERT @result SELECT @i, CASE WHEN @offset > 0 THEN LEFT(@text, @offset - 1) ELSE @text END
SET @text = CASE WHEN @offset > 0 THEN SUBSTRING(@text, @offset + LEN(@separator), LEN(@text)) END
END
RETURN
END
score:3
Another one of many string splitting functions out there. This is sort of similar to @Byron Whitlock's answer but instead of using master..spt_values uses a cte to generate a numbers table. SQL Server 2005 onwards.
CREATE TABLE dbo.Table1
(
Col1 CHAR(1),
Col2 CHAR(1),
Col3 CHAR(1),
Col4 VARCHAR(50)
)
GO
INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
GO
SELECT * FROM dbo.Table1;
GO
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, Col2, Col3,
LTRIM(RTRIM(SUBSTRING(valueTable.Col4, nums.n, charindex(N',', valueTable.Col4 + N',', nums.n) - nums.n))) AS [Value]
FROM Numbers AS nums INNER JOIN dbo.Table1 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col4)) AND SUBSTRING(N',' + valueTable.Col4, n, 1) = N','
score:8
I think you can do this:
SELECT
T.id, RIGHT(LEFT(T.csv,Number-1),
CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1))))
FROM
master..spt_values,
your_table T
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1
AND
(SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1) = '')
Code was shamelessly stolen from this site.
More questions
- Split one column into multiple rows
- Split one row into multiple rows base on column name
- How to split comma delimited data from one column into multiple rows
- Merge multiple rows into one column without duplicates
- Merge multiple rows into one column using carriage return line feeds
- How do you concat multiple rows into one column in SQL Server?
- SQL : Split one row into two rows depending on column
- Splitting data from one column into multiple rows in SQL Server
- Split one column into multiple columns in SQL Server
- SQL Server Split one column into multiple columns
- Convert multiple rows for one column into multiple columns (on the fly)
- SQL Server merge multiple rows into one column
- Group by column and multiple Rows into One Row multiple columns
- Split one column value into multiple column values
- How to combine multiple rows into one row and multiple column in SQL Server?
- Split column data into multiple rows
- SQL Split One Row To Multiple Rows Based on Column Number
- Can I concat ONLY one column within a larger SQL query OR transpose multiple rows into extra columns?
- Inserting multiple rows into SQL Server 2008 table, copied from other rows with one column changed
- Using SQL SMS, how do I combine multiple rows into one row, but maintain all the column data?
- Concatenate single column from multiple rows into a one column with distinct keywords
- Split one column into multiple column
- sql query to split a single column value into multiple rows
- SQL UPDATE statement combining multiple rows into one column
- In SQLAlchemy how to merge multiple rows into one by converting unique column values into comma separated string?
- Selecting multiple rows of many to many related columns into one column in SQL SERVER?
- SQL Server : split row into multiple rows based on a column value
- SQL Server Transpose (maybe pivot?) multiple rows into one column
- Inserting rows into a table with one IDENTITY column only
- SQL Server: combining multiple rows into one row
More questions with similar tag
- Math operation in SQL without select?
- Testing connection to server on Android
- Editing a large dataset for SQLBulkCopy into a SQL Server database
- Select and convert column values in SQL Server result set
- Connection -MongoDB & SQL Server
- SQL close close Gaps in data over time
- Insert data to multiples tables in ASP.NET API
- When/ why would you use QUOTENAME in SQL?
- Stored Procedure updates where last modified equals getdate but it also updates getdate trigger
- Creating blocks within a CTE - SQL Server
- Display if picture was uploaded or not for a particular id using group by
- Azure Database Columns not show up in SQL Management Studio 2014
- Create a table with array column ms sql
- Is it possible to have the "IF" on a "select" statement or using "Case"?
- Select only rows where time difference is x hours
- How do I update rows using while loop?
- SqlCommand with Parameters
- Does DataSet occupy too much space?
- Inserting using CSV
- Access and SQL Server calculate date differently
- SQL Server CEILING of 100 = 101?
- Use ISNULL or "LIKE"
- SqlDataSource run stored procedure twice
- php SQL Server mssql_select_db error
- Calculate week of month starting Monday
- How to use SqlCommand to CREATE DATABASE with parameterized db name?
- Is it possible to insert data to 2 tables from 1 controller?
- Making columns from one column's data in SQL Server
- String padding in tsql
- Assign T-SQL variable from CASE statement referencing previously declared variables