T-SQL Split Word into characters

Question

I have searched everywhere and I cannot find this implementation anywhere.

Let's say I have the word: QWERTY

I want to obtain this table:

Q
W
E
R
T
Y

Or for QWERTY AnotherWord I want to obtain

Q
W
E
R
T
Y
[space character here]
A
n
o
t
h
e
r
W
o
r
d

This is a duplicate of http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Petar Ivanov, Dec 15 '11 at 09:34
Sorry, the google and stackoverflow search didn't return any userful results. On the other side, I see that the answer given here is better and faster and it better fits my need of a solution. Also, I think it is more straight forward. — Dragos Durlut, Dec 15 '11 at 09:59
@PetarIvanov This is not a duplicate. The question you linked is about splitting values on a *delimiter*. There is no delimiter in use here. — Jonathan Wilson, Mar 11 '19 at 19:28

score 36 · Accepted Answer · edited Dec 15 '11 at 10:37

36

Do it like this:

select substring(a.b, v.number+1, 1) 
from (select 'QWERTY AnotherWord' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'

edited Dec 15 '11 at 10:37

aF.

62,344
41
131
195

answered Dec 15 '11 at 09:37

t-clausen.dk

42,087
11
52
90

hi @t-clausen.dk, the solution is not working in azure sql database. Do you have any other options? – Magendran V Nov 27 '17 at 14:16
1

@MagendranV it's because azure doesn't have spt_values, but its only purpose here is to act as a list of numbers up to 2047; use a row generator instead, like BabiBN's answer (safer than relying on undocumented spt_values) – Caius Jard Apr 27 '20 at 19:40
combining the answer from https://stackoverflow.com/questions/21425546/ you can use following query as option in Azure SQL database select substring(a.b, v.number+1, 1) from (select 'QWERTY AnotherWord' b) a join ( SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n number FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n) ) v on v.number < len(a.b) ORDER BY v.number – Sam Jul 31 '21 at 21:25

score 8 · Answer 2 · answered Jun 28 '17 at 08:42

8

Declare @word nvarchar(max)
Select @word = 'Hello This is the test';

with cte (Number)as 
(Select 1
union all 
select Number +1 From cte  where number <len(@word)
)
select * from Cte Cross apply (Select SUBSTRING(@word,number,1 ) ) as J(Letter)

answered Jun 28 '17 at 08:42

BabiBN

81
1
2

1

Generally, answers are much more helpful if they include an explanation of what the code is intended to do, and why that solves the problem without introducing others. – Peter Jun 28 '17 at 08:50
Good solution, using cross apply and a cte. – Matthias Jan 19 '18 at 12:21

score 4 · Answer 3 · answered Dec 15 '11 at 09:35

4

Here you have it:

create table #words (
  character varchar(1)
)

declare @test varchar(10)
select @test = 'QWERTY'

declare @count int, @total int
select @total = len(@test), @count = 0

while @count <= @total
begin
  insert into #words select substring(@test, @count, 1)
  select @count = @count + 1
end

select * from #words

drop table #words

answered Dec 15 '11 at 09:35

aF.

62,344
41
131
195

Thank, you. It works as well, but I want to avoid using temporary tables. I guess I could use variable tables. +1 – Dragos Durlut Dec 15 '11 at 09:41
See my answer for a table-value function version of this – Jonathan Wilson Mar 11 '19 at 19:55

score 3 · Answer 4 · answered Mar 11 '19 at 19:51

Here is a table-valued function (derived from aF's temp table implementation). It differs slightly from aF's implementation in that it starts with @count=1; this excludes an extraneous leading space.

CREATE FUNCTION [dbo].[Chars] (@string VARCHAR(max)) 
RETURNS @chars TABLE (character CHAR) 
AS 
  BEGIN 
      DECLARE @count INT, 
              @total INT 

      SELECT @total = Len(@string), 
             @count = 1 

      WHILE @count <= @total 
        BEGIN 
            INSERT INTO @chars 
            SELECT Substring(@string, @count, 1) 

            SELECT @count = @count + 1 
        END 

      RETURN 
  END

Usage:

SELECT * FROM dbo.chars('QWERTY AnotherWord')

M. Kirk Jennings · Answer 5 · 2015-10-01T15:43:51.860

Please, PLEASE avoid referencing systems tables, specifically system tables in system databases. In fact, the selected answer above probably won't compile in a Visual Studio 2013 Database Project

Table variables are fine, but recursion with a CTE is the answer:

DECLARE @str VARCHAR(max)
SET @str = 'QWERTY AnotherWord'
WITH Split(stpos,endpos)
AS(
SELECT 1 AS stpos, 2 AS endpos
UNION ALL
SELECT endpos, endpos+1
FROM Split
WHERE endpos <= LEN(@str)
)
SELECT 
    'character' = SUBSTRING(@str,stpos,COALESCE(NULLIF(endpos,0),LEN(@str)+1)-stpos)
   ,'charindex' = stpos
FROM Split

That said, the use for the code above is to get a table full of letters representing different permissions for a user. That is not the way to do this. Make a table with an ID, a permission code and a description then make a linking table between the users table and the new permissions table. this gives you the same abilities and doesn't make you solve dumb problems like this.

T-SQL Split Word into characters

5 Answers5

Linked

Related