CODEWITHSUNDEEP
stringt-sqlsplit
Dragos Durlut
Dragos Durlut

Reputation: 8098

T-SQL Split Word into characters

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

Upvotes: 20

Views: 31158

Answers (8)

dakab
dakab

Reputation: 5875

With table variable incl. position and while loop iterator:

DECLARE @Word VARCHAR(99) = 'QWERTY AnotherWord';
DECLARE @Chars TABLE ([Pos] TINYINT IDENTITY(1,1), [Char] CHAR(1));
DECLARE @i TINYINT = 0;

WHILE @i < LEN(@Word)
BEGIN
    SET @i += 1;
    INSERT INTO @Chars ([Char])
        SELECT SUBSTRING(@Word,@i,1);
END

SELECT [Pos], [Char] FROM @Chars;

Upvotes: 0

Sean
Sean

Reputation: 91

I like the use of REPLICATE() and substring in the answer by @drrollergator. I find value in the answer below, in accounting for:

  • The truncation to 8000 characters mentioned by Microsoft learn/docs. Explicitly casting to a larger datatype will avoid this.
  • the unordered ROW_NUMBER as mentioned in [https://stackoverflow.com/questions/44105691/row-number-without-order-by].

Sample SQL:

DECLARE @str NVARCHAR(MAX) = N'QWERTY AnotherWord'
SELECT
ss.[value]
FROM
(   SELECT TOP(LEN(@str))
        SUBSTRING(@str,n.[i],1) [value]
        ,n.[i]
    FROM ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT '.')) [i] FROM STRING_SPLIT(REPLICATE(CAST('.' AS VARCHAR(MAX)),LEN(@str) - 1),'.') ) n([i])
    /* [A.] Generate numbers equal to character count in @expression */
    ORDER BY n.[i]  
    /* [B.] Return 1-Char-Substring for each number/position */
) ss

Upvotes: 0

drrollergator
drrollergator

Reputation: 11

I wanted to contribute my own solution to this problem.

Convert into table valued function as desired (and handle nulls however you wish)

DECLARE @str nvarchar(100) = 'QWERTY AnotherWord'
DECLARE @len int = LEN(@str)-1;

--create a string of len(@str)-1 commas
--because STRING_SPLIT will return n rows for n-1 commas
--split string to return a table of len(@str) rows
--provide an index column named [index]
WITH [rows] AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY [value]) [index] 
    FROM STRING_SPLIT(REPLICATE(',', @len), ',')
), 
--for each row, take the index number
--and extract the character from that index
[split] AS (
    SELECT 
        [index],
        SUBSTRING(@str,[index],1) [char]
    FROM [rows]
)
--maintain the same order
--and return just the extracted characters
SELECT 
    --[index],
    [char]
FROM [split]
ORDER BY [index] ASC

output:

char
----
Q
W
E
R
T
Y
 
A
n
o
t
h
e
r
W
o
r
d

Upvotes: 1

Jonathan Wilson
Jonathan Wilson

Reputation: 4295

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')

Upvotes: 3

BabiBN
BabiBN

Reputation: 81

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)

Upvotes: 8

M. Kirk Jennings
M. Kirk Jennings

Reputation: 29

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.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

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'

Upvotes: 38

aF.
aF.

Reputation: 66697

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

Upvotes: 4

Related Questions