SQL拆分函数 [英] SQL Split function
本文介绍了SQL拆分函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找一种方法来做到这一点......
I'm looking for a way to do this ...
SELECT FirstName, LastName, Split(AddressBlock, ' ', 1), Split(AddressBlock, ' ', 2), PostCode
FROM Contacts
我想传递的参数是...
The arguments I want to pass are ...
- 地址
- 分隔符(当前情况需要 2 个空格,但这可能是一个逗号或一个空格后跟一个逗号)或其他内容(它会有所不同).
- 我想返回的地址部分(我并不总是需要拆分结果的所有部分).
我似乎能够在互联网上找到一些拆分函数的示例,但它们返回一个包含整个拆分部分集的表.
I seem to be able to find a few examples of splitting functions about the internet but they return a table containing the entire set of split parts.
我的 SQL 技能不是很好,所以我需要非常简单的答案.我一直在处理 nvarchar 数据,并且该函数需要可重用.
My SQL skills aren't that great so I need the answer to be ultra simple. I'm always working with nvarchar data and the function needs to be reusable.
推荐答案
如果您想要一个用户定义的函数来执行此操作,这应该可行.不是很漂亮,但是...
If you want a user-defined function to do this, this should work. Not that pretty, but...
CREATE FUNCTION dbo.SplitStringPart (
@input nvarchar(MAX),
@separator nvarchar(10),
@index int
) RETURNS nvarchar(MAX)
BEGIN
DECLARE @counter int,
@position int,
@oldposition int,
@separatorlength int,
@result nvarchar(MAX)
SET @separatorlength = DATALENGTH(@separator) / 2
IF @separatorlength = 0 RETURN NULL
SET @result = NULL
SET @counter = 1
SET @position = -2
WHILE (@counter <= @index)
BEGIN
SET @oldposition = @position
SET @position = CHARINDEX(@separator, @input, @position + 1)
IF @position = 0 AND @counter < @index
BEGIN
SET @oldposition = 0
BREAK
END
SET @counter = @counter + 1
END
IF @oldposition = 0 AND @position = 0
RETURN NULL
ELSE IF @oldposition < 0
BEGIN
IF @position = 0 AND @index = 1
SET @result = @input
ELSE
SET @result = SUBSTRING(@input, 0, @position)
END
ELSE IF @position <= 0
SET @result = SUBSTRING(@input, @oldposition + @separatorlength, LEN(@input) - @oldposition - @separatorlength)
ELSE
SET @result = SUBSTRING(@input, @oldposition + @separatorlength, @position - @oldposition - @separatorlength)
RETURN @result
END
GO
这篇关于SQL拆分函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文