如何在sql server中划分一个名称到行?

Question

我想要一个名字作为参数传递给一个存储过程，当我执行这个存储过程时，它应该把这个名字分成几行。例如，如果传递'Peter'，输出应该是

p
e
e
r

Answer 1

要做到这一点，最快的方法是使用一个计数和SUBSTRING。我假设名称不会超过100个字符，并且(因为它是一个名称)它是nvarchar而不是varchar。然后你可以这样做:

DECLARE @Name nvarchar(100) = N'Peter';
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP(DATALENGTH(@Name) / 2) --If a varchar, remove the / 2
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2)
SELECT SUBSTRING(@Name,T.I,1) AS C
FROM Tally T;

Answer 2

declare @input varchar(max);
set @input = 'Peter'
declare @table TABLE (char varchar(1));

while (LEN(@input)> 0)
begin
insert into @table select substring(@input,1,1)
select @input = RIGHT(@input,Len(@input)-1) 
end
select * from @table
GO

| char ||:——|| p || e || t || e || r |

declare @T table
(
ID int identity,
names varchar(10)
)
insert into @T
select 'Peter' 
;with cte as 
(
select ID,
left(names, 1) as Data,
stuff(names, 1, 1, '') as remains
from @T
where len(names) > 0
union all
select ID,
left(remains, 1) as Data,
stuff(remains, 1, 1, '') as remains
from cte
where len(remains) > 0
)
select ID,
Data
from cte
order by ID

ID |数据-: |:——1 | p1/21 | t1/21 | r

db<此处小提琴>