Thread: Split on Space Function....

There are many times when you might want to split out a first or last name from a string where the first and last names are together in one field (I get lists from people like this all the time). This can be a frustrating problem. I have a function that I can use to split a string on the space character (you could alter this to accept almost any delimiter). Do with it what you will

You can call it like so:

Code:

SELECT dbo.fn_Split('John Jacob Jingle Heimer Schmidt', 'Last') -- Returns Schmidt

or

Code:

SELECT dbo.fn_Split('John Jacob Jingle Heimer Schmidt', 1) -- Returns John

So you can specify the record, or simply call it with 'Last' to get the last record.

Code:

CREATE FUNCTION [dbo].[fn_Split] (@String varchar(7800), @RecNum varchar(10))
RETURNS varchar(7800)

BEGIN --Begin Function

DECLARE @p1 int -- The first position or beginning of candidate string.
DECLARE @p2 int -- The last position or end of the candidate string.
DECLARE @CStr varchar(7800) -- The string being evaluated (or candidate string).
DECLARE @trTextStr varchar(7800) -- A trimmed version of the input string.
DECLARE @sLen int -- The length of the trimmed string.
DECLARE @endEval bit -- Used to end the string evaluation.
DECLARE @RetString varchar(7800)
/*testing
DECLARE @String varchar(8000)
SET @String = 'John Jacob Jingle Heimer Schmidt'
*/
SET @trTextStr = LTRIM(RTRIM(@String))
IF @trTextStr IS NULL
BEGIN
   RETURN NULL
END

DECLARE @Table table
(
    [id] int IDENTITY(1,1) PRIMARY KEY,
    [string] varchar(7800)
)

SET @endEval = 0

IF CHARINDEX(' ', @trTextStr) = 0
  BEGIN
	INSERT INTO @Table VALUES(@trTextStr)
    SET @RetString = (SELECT string FROM @Table WHERE id = CASE @RecNum WHEN 'Last' THEN (SELECT MAX(id) FROM @Table) ELSE CAST(@RecNum as int) END)
    RETURN @RetString
  END

-- Set initial position.
SET @p1 = 1
SET @p2 = CHARINDEX(' ', @trTextStr)

-- Set initial values.
SET @sLen = LEN(@trTextStr)
SET @CStr = LTRIM(RTRIM(SUBSTRING(@trTextStr, @p1, @p2 - @p1)))

INSERT INTO @Table VALUES(@CStr)

WHILE @endEval = 0
  BEGIN
    SET @p1 = @p2
    SET @p2 = CHARINDEX(' ', LTRIM(RTRIM(SUBSTRING(@trTextStr, @p1 + 1, @sLen - @p1))))

    IF @p2 = 0
      BEGIN
        SET @p2 = @sLen + 1
      END -- If there is no space left, we should evaluate from p1 to the end of the string.

    IF @p2 <> @sLen + 1
      BEGIN
        SET @p2 = (@p1 + 1) + @p2
      END

    SET @CStr = RTRIM(LTRIM(SUBSTRING(@trTextStr, @p1, (@p2 - @p1))))
 
	INSERT INTO @Table VALUES(@Cstr)

    IF @p2 = @sLen + 1
      BEGIN
        SET @endEval = 1
      END
  END -- End While

SET @RetString = (SELECT string FROM @Table WHERE id = CASE @RecNum WHEN 'Last' THEN (SELECT MAX(id) FROM @Table) ELSE CAST(@RecNum as int) END)

RETURN @RetString
END