用于拆分连接字符串的函数 [英] function to split concatenated string

问题描述

我有一个关键字表格，我在关键字列中有

商业解决方案+班加罗尔的商业解决方案+迈索尔的商业解决方案

我想用函数分割这些关键字..如何实现这一点。请提供解决方案。

I have a keywords table where in keywords column i have
Business Solutions + Business Solutions in Bangalore + Business Solutions in Mysore

I want to split these keywords using functions.. How to achieve this. Pls provide the solution.

推荐答案

尝试这篇文章 [ ^ ]

创建以下功能

Hi,

Create below function

CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END

上述函数将返回一个临时表。< br / >


示例代码：

The above function will return a temporary table.

Sample code:

select * from [dbo].[String_Tokenizer]('Business Solutions + Business Solutions in Bangalore + Business Solutions in Mysore','+')

输出：

Output:

Data

Business Solutions
Business Solutions in Bangalore
Business Solutions in Mysore

如果您有这方面的知识，并且有可能这样做，我建议您使用您可能需要的CLR用户定义函数创建一个.net库（请参阅： http://msdn.microsoft.com/en-us/library/ w2kae45k（v = vs.80）.aspx [ ^ ]）。在.net中，你有更多的可能来解决这些简单的任务。

这是一个例子，用来制作表值clr udf： http://msdn.microsoft.com/en-us/library/ms131103.aspx [ ^ ]

If you have the knowledge, and the possibility to do that, I suggest, you make a .net library with CLR User Defined Functions you might need (see: http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx[^]). In .net, you have so much more possibilities to resolve such simple tasks.
Here is an example, ho to make table-valued clr udf: http://msdn.microsoft.com/en-us/library/ms131103.aspx[^]

这篇关于用于拆分连接字符串的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！