SQL分割功能 [英] SQL Split function
问题描述
选择名字,姓氏,分割(地址块,'' ',1),Split(AddressBlock,'',2),PostCode
FROM Contacts
我想传递的参数是...
- 地址
- 分隔符(当前情况需要2个空格,但这可能是一个逗号或空格后跟一个逗号)或其他(不同)。
- 我想返回的地址部分(我并不总是需要拆分结果的所有部分)。
- 分隔符(当前情况需要2个空格,但这可能是一个逗号或空格后跟一个逗号)或其他(不同)。
我似乎能够找到一些关于互联网拆分功能的例子,但他们返回一个包含整个拆分部分集合的表。
我的SQL技能并不是那么好,所以我需要的答案非常简单。
我总是使用nvarchar数据,并且该函数需要可重用。
如果您想要用户 - 定义的功能来做到这一点,这应该工作。不是那么漂亮,但... ...
$ b $ pre $ CREATE FUNCTION dbo.SplitStringPart(
@input nvarchar(MAX),
@separator nvarchar(10),
@index int
)RETURNS nvarchar(MAX)
BEGIN
DECLARE @counter int,
@位置int,
@oldposition int,
@separatorlength int,
@result nvarchar(MAX)
SET @separatorlength = DATALENGTH(@separator)/ 2
IF @separatorlength = 0返回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如果@oldposition< 0
BEGIN
IF @position = 0 AND @index = 1
SET @result = @input
ELSE
SET @result = SUBSTRING(@input,0, @position)(@input,@oldposition + @separatorlength,LEN(@input) - @oldposition - @separatorlength(@inposition)
END
ELSE如果@position <= 0
SET @result = SUBSTRING )
ELSE
SET @result = SUBSTRING(@input,@oldposition + @separatorlength,@position - @oldposition - @separatorlength)
RETURN @result
END
GO
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 ...
- The address
- The separator (current situation requires 2 spaces but this might be a comma or a space followed by a comma) or something else (it varies).
- The address part I want to return (i don't always need all parts of the split result).
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.
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屋!