InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 23:22:22 GMT20http://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspx[quote][b]Vedran Kesegic (11/19/2012)[/b][hr]It updates (sets) variables to null.[/quote]Understood but it's still not a quirky update. Not even close to being one.Mon, 19 Nov 2012 06:45:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspxIt updates (sets) variables to null.Mon, 19 Nov 2012 06:38:57 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspx[quote][b]Vedran Kesegic (11/19/2012)[/b][hr]Your @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.[/quote]Look again... it's not a quirky update because the code doesn't actually update any data.Mon, 19 Nov 2012 06:36:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspxYour @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.Mon, 19 Nov 2012 00:22:28 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspxFor starters, your UPDATE code isn't updating anything in the table.Sun, 18 Nov 2012 11:48:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1386056-391-1.aspxI am using SQL Server 2008 R2. I created a User Defined Function like this:[quote]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 inputRETURNS @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 RETURNEND[/quote]I have a table named "CUSTOM_test" with two columns:ID TitleItem1 Lord of the RingsItem2 The HobbitsItem3 Dark Knight RisesWhen I write code like this, the value of @word is "Lord":[quote]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)[/quote]But when I write code like this, the value of @word is NULL:[quote]DECLARE @title nvarchar(100)DECLARE @word nvarchar(20)UPDATE CUSTOM_testSET @title = Title, @word = (SELECT Data FROM Custom_StringToTableWithID(@title, ' ') WHERE ID = 1)WHERE ID = 'Item1'[/quote]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..Sun, 18 Nov 2012 09:35:59 GMThenryvuong1000