SQL Split function

Question

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.

possibly a duplicate of stackoverflow.com/questions/314824/… — Unreason
– Unreason, Commented May 20, 2010 at 9:18
I disagree, on the basis that I only want to extract certain parts of the plist result and do not wish to retrieve a table variable. I want a single string each time the function is called. However there is a solution on there that might be a good start. — War
– War, Commented May 20, 2010 at 9:26

David M · Accepted Answer · 2010-05-20 09:48:57Z

4

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

edited May 20, 2010 at 9:48

answered May 20, 2010 at 9:30

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

War Over a year ago

It does break when the string only has 1 part and i ask for part 2 though ... not ideal.

Neil Knight · Accepted Answer · 2010-05-20 09:19:42Z

1

It's not pretty, but add this to you SQL statement and it should work:

CASE 
WHEN charindex(' ', substring(AddressBlock, (charindex(' ', AddressBlock) + 1), len(AddressBlock))) > 0 THEN substring(AddressBlock, (charindex(' ', AddressBlock) + 1), charindex(' ', substring(AddressBlock, (charindex(' ', AddressBlock) + 1), len(AddressBlock))) - 1)
ELSE substring(AddressBlock, (charindex(' ', AddressBlock) + 1), len(AddressBlock))    
END AS 'Address 1', 
CASE WHEN charindex(' ', substring(AddressBlock, (charindex(' ', AddressBlock) + 1), len(AddressBlock))) > 0 THEN substring(AddressBlock, charindex(' ', AddressBlock) + charindex(' ', substring(AddressBlock, (charindex(' ', AddressBlock) + 1), len(AddressBlock))) + 1, Len(AddressBlock))
ELSE ''
END AS 'Address 2'

answered May 20, 2010 at 9:19

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

3 Comments

War Over a year ago

Doesn't this look for a single char on which to perform a split? I need to split on a string value (eg ", ") ... interesting though ...

Neil Knight Over a year ago

This looks up the string value within the existing column and strips out everything up to the string value. So, change the charindex(' ' to charindex(',' if you want comma seperated.

War Over a year ago

It wasn't exactly what I asked for but a good effort so you got my vote :)

Vinnie Amir · Accepted Answer · 2017-03-17 02:48:17Z

0

Here is my version of the answer. This is MUCH faster and robust. No need to fuss around with substrings, charindex, etc.

CREATE FUNCTION [dbo].[SplitArray]
(
    @RowToSplit nvarchar(MAX),
    @Delimeter nvarchar(MAX)
)  
RETURNS @RtnValue table (ID bigint IDENTITY, Data nvarchar(MAX)) 
AS  
BEGIN 
    DECLARE @xml xml
    SET @xml = '<field>' + REPLACE(@RowToSplit, @Delimeter, '</field><field>') + '</field>'
    INSERT INTO @RtnValue(data)
    SELECT  tbl.c.value('.','nvarchar(max)')
    FROM @xml.nodes('/field') tbl(c)
    RETURN
END

You would simply use the resultant split values in a table, like this:

SELECT Data FROM dbo.SplitArray('this is great',' ')

This will return:

Data
============
this
is
great

answered Mar 17, 2017 at 2:48

Vinnie Amir

6217 silver badges13 bronze badges

2 Comments

War Over a year ago

So let me get this right ... To get a piece of a string you split it up then build a bigger string of xml, then parse that xml, then query it, and sql will do this faster ... What a weird tech SQL is if this true!

Vinnie Amir Over a year ago

The xml is a binary object so it's not quite correct. Run this with query analyser and you will see. Character index is slow as it is string based, using xml it is binary based.

Collectives™ on Stack Overflow

SQL Split function

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related