I need to split a string in a column into one character each into it's own column in SQL Server 2012.
Example: if I have a column with 'ABCDE', I need to split it into 'A', 'B', 'C', 'D', 'E', with each of these into their own columns.
The length of the column to be split may vary, so I need this to be as dynamic as possible.
My question is different from the other post (Can Mysql Split a column?) since mine doesn't have any delimiters. Thanks
I am interpreting the question as putting the characters into one column ("split a string in a column into one character each into it's own column"). However, I realize that this might be ambiguous.
One method is with a recursive CTE:
with chars as (
select left(val, 1) as c, substring(val, 2, len(val)) as rest
from (select 'ABCDE' as val union all select '123') t
union all
select left(rest, 1), substring(rest, 2, len(rest))
from chars
where rest <> ''
)
select c
from chars;
Just plug in your table and column in the subquery. Note that you might want to include other columns as well.
Here is a SQL Fiddle.
If you want multiple columns and the number is not fixed, then you will need dynamic SQL.
You can do this like this:
DECLARE @t TABLE(id int, n VARCHAR(50))
INSERT INTO @t VALUES
(1, 'ABCDEF'),
(2, 'EFGHIJKLMNOPQ')
;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM @t
UNION ALL
SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)
SELECT *
FROM cte
PIVOT (MAX(c) FOR ind IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[12],[13],[14],[15])) p
Output:
id n 1 2 3 4 5 6 7 8 9 10 12 13 14 15
1 ABCDEF A B C D E F NULL NULL NULL NULL NULL NULL NULL NULL
2 EFGHIJKLMNOPQ E F G H I J K L M N P Q NULL NULL
Here is dynamic version:
DECLARE @l INT, @c VARCHAR(MAX) = ''
SELECT @l = MAX(LEN(n)) FROM PivotTable
WHILE @l > 0
BEGIN
SET @c = ',[' + CAST(@l AS VARCHAR(MAX)) + ']' + @c
SET @l = @l - 1
END
SET @c = STUFF(@c, 1, 1,'')
DECLARE @s NVARCHAR(MAX) = '
;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM PivotTable
UNION ALL
SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)
SELECT *
FROM cte
PIVOT (MAX(c) FOR ind IN(' + @c + ')) p'
EXEC (@s)
If you want a new column for every character you simply need:
SELECT [1] = SUBSTRING(Col, 1, 1),
[2] = SUBSTRING(Col, 2, 1),
[3] = SUBSTRING(Col, 3, 1),
[4] = SUBSTRING(Col, 4, 1),
[5] = SUBSTRING(Col, 5, 1),
[6] = SUBSTRING(Col, 6, 1),
[7] = SUBSTRING(Col, 7, 1),
[8] = SUBSTRING(Col, 8, 1),
[9] = SUBSTRING(Col, 9, 1)
FROM (VALUES ('ABCDE'), ('FGHIJKLMN')) t (Col);
Which is fine, if you have a know number of columns. If you have an unknown number of columns, then you just need to generate the same SQL with n columns. To do this you will need a numbers table, and since many people do not have one, I will do a quick demo on how to dynamically generate one.
The below will generate a sequential list of numbers, 1 - 100,000,000.
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT Number
FROM Numbers;
It simply uses a table valued constructor to generate 10 rows (N1), then cross joins these 10 rows to get 100 rows (N2), then cross joins these 100 rows to get 10,000 rows (N3) and so on and so on. It finally uses ROW_NUMBER() to get the sequential numbers.
This probably needs to be cut down for this use, I hope you are not splitting a string that is 100,000,000 characters long, but the principle applies. You can just use TOP and the maximum length of your string to limit it. For each number you can just build up the necessary repetetive SQL required, which is:
,[n] = SUBSTRING(Col, n, 1)
So you have something like:
SELECT Number,
[SQL] = ',[' + CAST(Number AS VARCHAR(10)) + '] = SUBSTRING(Col, ' + CAST(Number AS VARCHAR(10)) + ', 1)'
FROM Numbers;
Which gives something like:
Number SQL
-----------------------------------
1 ,[1] = SUBSTRING(Col, 1, 1)
2 ,[2] = SUBSTRING(Col, 2, 1)
3 ,[3] = SUBSTRING(Col, 3, 1)
4 ,[4] = SUBSTRING(Col, 4, 1)
The final step is to build up your final statement by concatenating all the text in the column SQL; the best way to do this is using SQL Server's XML Extensions.
So your final query might end up like:
DECLARE @SQL NVARCHAR(MAX) = '';
IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T (Col VARCHAR(100));
INSERT #T (Col) VALUES ('ABCDE'), ('FGHIJKLMN');
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT @SQL = 'SELECT Col' +
( SELECT TOP (SELECT MAX(LEN(Col)) FROM #T)
',[' + CAST(Number AS VARCHAR(10)) + '] = SUBSTRING(Col, ' + CAST(Number AS VARCHAR(10)) + ', 1)'
FROM Numbers
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') + '
FROM #T;';
EXECUTE sp_executesql @SQL;
Which gives:
Col 1 2 3 4 5 6 7 8 9
-------------------------------------------------
ABCDE A B C D E
FGHIJKLMN F G H I J K L M N
Finally, if you actually wanted to split it into rows, I would still use the same approach, with your adhoc numbers table, just join it to your original table:
IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T (Col VARCHAR(100));
INSERT #T (Col) VALUES ('ABCDE'), ('FGHIJKLMN');
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT TOP (SELECT MAX(LEN(Col)) FROM #T) ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT t.Col,
Position = n.Number,
Character = SUBSTRING(t.Col, n.Number, 1)
FROM #T AS t
INNER JOIN Numbers AS n
ON n.Number <= LEN(t.Col)
ORDER BY t.Col, n.Number;
Which gives something like:
Col Position Character
-------------------------------
ABCDE 1 A
ABCDE 2 B
ABCDE 3 C
ABCDE 4 D
ABCDE 5 E
One way
declare @str varchar(max) = 'ABCDE'
declare @sql nvarchar(max) = ''
declare @i int = 1
while (@i <= len(@str)) begin
set @sql += case when @i > 1 then ',' else '' end + '''' + substring(@str, @i, 1) + ''''
set @i += 1
end
exec('select ' + @sql)
(If ' can appear as a char you would need to substitute '')
This is a solution for a dynamic text length.
-- Generate demo data
CREATE TABLE #temp(col nvarchar(100))
INSERT INTO #temp(col)
VALUES(N'A'),(N'ABC'),(N'DEFGHI'),(N'AB'),(N'KLOMA')
-- Split all in multiple rows
CREATE TABLE #output (col nvarchar(100),part nchar(1), pos int)
;WITH cte AS(
SELECT col, LEFT(col, 1) as part, 1 as pos
FROM #temp
UNION ALL
SELECT col, SUBSTRING(col, pos+1,1) as part, pos+1 as part
FROM cte
WHERE LEN(col) > pos
)
INSERT INTO #output(col, part, pos)
SELECT col, part, pos
FROM cte
DECLARE @sql nvarchar(max), @columnlist nvarchar(max)
-- Generate Columlist for dynamic pivot
SELECT @columnlist = COALESCE(@columnlist + N',[' + CONVERT(nvarchar(max),pos) + ']', N'[' + CONVERT(nvarchar(max),pos) + ']')
FROM #output o
WHERE o.col = (SELECT TOP (1) col FROM #output ORDER BY LEN(col) DESC)
-- Pivoting for readability
SET @sql = N'
SELECT pvt.*
FROM #output o
PIVOT (
MAX(o.part)
FOR pos IN('+@columnlist+')
) as pvt'
EXEC (@sql)
-- Cleanup
DROP TABLE #temp
DROP TABLE #output
The keypart is the cte and the pivoting afterwards. If you have any questions, just give me a short feedback.
