split string in to rows in sql server

A loop obviously comes to mind but we can do much better. This is where a tally or numbers table is perfect. I have a view in my system like this which creates such a table with 10,000 rows on demand. There are plenty of ways to create such a table, or you could create a persistent table for a slight performance gain.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Now that we have the view this type of thing is super painless using a totally set based approach and abandoning the idea of looping.

declare @Something varchar(20) = 'ABCDEF'

select SUBSTRING(@Something, N, 1)
from cteTally t
where t.N < LEN(@Something)

Solving this is trivial if you have a Numbers table. No loops or cursors are necessary, resulting in performance that is orders of magnitude better than other solutions:

declare @name varchar(10)='ABCDEF'

select @name,SUBSTRING(@name,n,1)
from numbers
where n<=LEN(@name)

Or

select EMP.First_Name,SUBSTRING(EMP.First_Name,n,1)
from EMP,numbers
where n<=LEN(EMP.First_Name)

A Numbers table contains only numbers from 1 to a sufficiently large number. You can create such a table with the following statement (borrowed from the linked article):

SELECT TOP (1000000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
INTO dbo.Numbers
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(n)

You can use a CTE to parse out each character of your string onto a new row:

DECLARE @data varchar(200) = 'ABCDEF'

;WITH CTE AS (
    SELECT
        1 as CharacterPosition,
        SUBSTRING(@data,1,1) as Character
    UNION ALL
    SELECT
        CharacterPosition + 1,
        SUBSTRING(@data,CharacterPosition + 1,1)
    FROM
        CTE
    WHERE CharacterPosition < LEN(@data)
)
SELECT Character
FROM CTE