Split T-SQL word into characters

Question

I searched everywhere and I cannot find this implementation anywhere.

Say I have a word: QWERTY

I want to get this table:

 Q W E R T Y 

Or for QWERTY AnotherWord I want to get

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

Answer 1

Do it like this:

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

Answer 2

Here you have:

 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 

Answer 3

 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) 

Answer 4

Please PLEASE avoid references to system tables, in particular system tables in system databases. In fact, the selected answer above will probably not compile in the Visual Studio 2013 Database Project

Table variables are accurate, but recursion with 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 

However, using the above code is to get a table full of letters representing different permissions for the user. This is not a way to do this. Create a table with an identifier, permission code, and description, then create a link table between the user table and the new permission table. it gives you the same abilities and does not force you to solve such stupid problems like this.