November 18, 2012 at 9:36 am
I am using SQL Server 2008 R2. I created a User Defined Function like this:
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
I have a table named "CUSTOM_test" with two columns:
ID Title
Item1Lord of the Rings
Item2The Hobbits
Item3Dark Knight Rises
When I write code like this, the value of @word is "Lord":
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)
But when I write code like this, the value of @word is NULL:
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'
The later code is just a simplified version. I actually need to loop through the whole table and there's more code in that, but it cannot work as long as @word is null. Can someone give me an explanation why @word is null? Thanks.
.
November 18, 2012 at 11:48 am
For starters, your UPDATE code isn't updating anything in the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2012 at 12:22 am
Your @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.
November 19, 2012 at 6:36 am
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.
Look again... it's not a quirky update because the code doesn't actually update any data.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2012 at 6:38 am
It updates (sets) variables to null.
November 19, 2012 at 6:45 am
Vedran Kesegic (11/19/2012)
It updates (sets) variables to null.
Understood but it's still not a quirky update. Not even close to being one.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply