如何在sql server中拆分字符串 [英] How to split strings in sql server
本文介绍了如何在sql server中拆分字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在没有分隔符或分隔符的情况下拆分sql server中的字符串?
解决方案
给出的函数 here [ ^ ]将帮助你在任何角色上分割字符串。
此主题 [ ^ ]也有一些解决方案。
首先,创建一个函数
创建函数SplitString
(
@ str nvarchar(4000),
@separator char(1)
)
返回表
AS
return(
with tokens(p,a, b)AS(
选择
1,
1,
charindex(@separator,@ str)
union all
select
p + 1 ,
b + 1,
charindex(@ separator,@ str,b + 1)
来自代币
其中b> 0
)
选择
p-1 zeroBasedOccurance,
substring(
@str,
a,
情况,当b> 0然后ba ELSE 4000结束)
AS s
来自代币
)
GO
然后,将其用作表格
SELECT s FROM SplitString(' Hello John Smith',' ')
WHERE zeroBasedOccurance = 1
hi,
你可以使用这个功能,< br $>
创建 功能 [dbo]。[Split]
(
@ RowData nvarchar ( 2000 )
)
RETURNS @ RtnValue table
(
数据 nvarchar ( 500 )
)
AS
BEGIN
声明 @ Cnt int
声明 @ SplitOn nvarchar ( 5 )
声明 @seperator varchar ( 1 )
声明 @ specialCharcters nvarchar ( 50 )
set @ specialCharcters = ' 〜!@#/ blockquote>
How to split strings in sql server without separators or delimiters?
解决方案The function given here[^] will help you split string on any character.
This thread[^] has some solutions as well.
First, create a function
create function SplitString ( @str nvarchar(4000), @separator char(1) ) returns table AS return ( with tokens(p, a, b) AS ( select 1, 1, charindex(@separator, @str) union all select p + 1, b + 1, charindex(@separator, @str, b + 1) from tokens where b > 0 ) select p-1 zeroBasedOccurance, substring( @str, a, case when b > 0 then b-a ELSE 4000 end) AS s from tokens ) GO
Then, use it as table
SELECT s FROM SplitString('Hello John Smith', ' ') WHERE zeroBasedOccurance=1
hi ,
you can use this function ,
create FUNCTION [dbo].[Split] ( @RowData nvarchar(2000) ) RETURNS @RtnValue table ( Data nvarchar(500) ) AS BEGIN Declare @Cnt int Declare @SplitOn nvarchar(5) declare @seperator varchar(1) Declare @specialCharcters nvarchar(50) set @specialCharcters='~!@#
这篇关于如何在sql server中拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文