在 Sql Server 2005 中将字符串拆分为单个字符 [英] Split a string into individual characters in Sql Server 2005
本文介绍了在 Sql Server 2005 中将字符串拆分为单个字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个输入
ID data
1 hello
2 sql
所需的输出是
ID RowID Chars
1 1 H
1 2 e
1 3 l
1 4 l
1 5 o
2 1 s
2 2 q
2 3 l
我目前的做法是
Declare @t table(ID INT IDENTITY , data varchar(max))
Insert into @t Select 'hello' union all select 'sql'
--Select * from @t
;With CteMaxlen As(
Select MaxLength = max(len(data)) from @t)
, Num_Cte AS
(
SELECT 1 AS rn
UNION ALL
SELECT rn +1 AS rn
FROM Num_Cte
WHERE rn <(select MaxLength from CteMaxlen)
)
-- Shred into individual characters
, Get_Individual_Chars_Cte AS
(
SELECT
ID
,Row_ID =ROW_NUMBER() Over(PARTITION by ID Order by ID)
,chars
FROM @t,Num_Cte
CROSS APPLY( SELECT SUBSTRING((select data from @t),rn,1) AS chars) SplittedChars
)
Select * from Get_Individual_Chars_Cte
该查询根本不起作用,但有一个例外
The query is not working at all with an exception being
消息 512,级别 16,状态 1,第 4 行
子查询返回了 1 个以上的值.这在以下情况下是不允许的子查询跟随 =, !=, <, <= , >, >=或者当子查询用作表达.
Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
我找到了答案
;with Get_Individual_Chars_Cte AS
(
SELECT
ID,
Row_ID =ROW_NUMBER() Over(PARTITION by ID Order by ID)
,SUBSTRING(Data,Number,1) AS [Char]--,
FROM @t
INNER JOIN master.dbo.spt_values ON
Number BETWEEN 1 AND LEN(Data)
AND type='P'
)
Select * from Get_Individual_Chars_Cte
需要帮助
推荐答案
;with cte as
(
select ID,
substring(data, 1, 1) as Chars,
stuff(data, 1, 1, '') as data,
1 as RowID
from @t
union all
select ID,
substring(data, 1, 1) as Chars,
stuff(data, 1, 1, '') as data,
RowID + 1 as RowID
from cte
where len(data) > 0
)
select ID, RowID, Chars
from cte
order by ID, RowID
这篇关于在 Sql Server 2005 中将字符串拆分为单个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文