CREATE FUNCTION [dbo].[Custom_StringToTableWithID]
(
@string VARCHAR(MAX),
@delimiter CHAR(1)
)
--The return table has a column with auto-increment primary key and a column with text
--The text column is the result of the split string from the input
RETURNS @output TABLE( ID int identity primary key, Data VARCHAR(MAX))

BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
--Stripped off any new line character, carriage return character, leading and trailing spaces in the insert value
--Each new line and carrage return characters is replaced by a blank space
VALUES (LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@string, @start, @end - @start),CHAR(10), ' '), CHAR(13), ' '))))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN

END

DECLARE @title nvarchar(100)
SET @title = (SELECT Title FROM CUSTOM_test WHERE ID = 'Item1')
DECLARE @word nvarchar(20)
SET @word = (SELECT Data FROM Custom_StringToTableWithID(@title, ' ') WHERE ID = 1)

DECLARE @title nvarchar(100)
DECLARE @word nvarchar(20)
UPDATE CUSTOM_test
SET
@title = Title,
@word = (SELECT Data FROM Custom_StringToTableWithID(@title, ' ') WHERE ID = 1)
WHERE ID = 'Item1'

Vedran Kesegic (11/19/2012)

---

Your @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.

Vedran Kesegic (11/19/2012)

---

It updates (sets) variables to null.