question

javed79 avatar image
javed79 asked

Convert string 'NULL' to actual NULL

I have a table containing NULL as string. So basically it is not actual NULL. So basically I was trying to update the table with following query to convert string NULL to actual NULL. DECLARE @sql NVARCHAR(255); SET @sql = N''; SELECT @sql = @sql + ' ' + QUOTENAME(name) + ' = CASE WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE ' + QUOTENAME(name) + ' END,' FROM sys.columns WHERE [object_id] = OBJECT_ID('[dbo].[ServiceProvider]') AND system_type_id IN (35,99,167,175,231,239); SELECT @sql = N'UPDATE [dbo].[ServiceProvider] SET ' + LEFT(@sql, LEN(@sql)-1) + ';'; PRINT @sql exec sp_executesql @sql Which should create the dynamic format of a simple update, as below, UPDATE [dbo].[ServiceProvider] SET [SP_NAME] = CASE WHEN [SP_NAME] = 'NULL' THEN NULL ELSE [SP_NAME] END ,[STREET_ADDRESS] = CASE WHEN [STREET_ADDRESS] = 'NULL' THEN NULL ELSE [STREET_ADDRESS] END ,[TOWN_SUBURB] = CASE WHEN [TOWN_SUBURB] = 'NULL' THEN NULL ELSE [TOWN_SUBURB] END ,[AUTHORISED_CONTACT] = CASE WHEN [AUTHORISED_CONTACT] = 'NULL' THEN NULL ELSE [AUTHORISED_CONTACT] END ,[PHONE_NUMBER] = CASE WHEN [PHONE_NUMBER] = 'NULL' THEN NULL ELSE [PHONE_NUMBER] END ,[FAX_NUMBER] = CASE WHEN [FAX_NUMBER] = 'NULL' THEN NULL ELSE [FAX_NUMBER] END ,[EMAIL_ADDRESS] = CASE WHEN [EMAIL_ADDRESS] = 'NULL' THEN NULL ELSE [EMAIL_ADDRESS] END ,[SP_NOTES] = CASE WHEN [SP_NOTES] = 'NULL' THEN NULL ELSE [SP_NOTES] END ,[APPOINTMENT_INSTRUCTIONS] = CASE WHEN [APPOINTMENT_INSTRUCTIONS] = 'NULL' THEN NULL ELSE [APPOINTMENT_INSTRUCTIONS] END ,[EmailToList] = CASE WHEN [EmailToList] = 'NULL' THEN NULL ELSE [EmailToList] END But when I am running the dynamic code the output messages shows that the code is getting truncated like, UPDATE [dbo].[ServiceProvider] SET [SP_NAME] = CASE WHEN [SP_NAME] = 'NULL' THEN NULL ELSE [SP_NAME] END, [STREET_ADDRESS] = CASE WHEN [STREET_ADDRESS] = 'NULL' THEN NULL ELSE [STREET_ADDRESS] END, [TOWN_SUBURB] = CASE WHEN [TOWN_SUBURB] = 'NULL' THEN NU Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'NU'. My text output is set as 8192 the highest supported by SQL I guess. Any suggestion how to execute this code in full length without truncation?
convertnull
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
Change the size of your parameter. You are limiting your dynamic query to 255 characters in that parameter. For a large query such as this you would need something much larger. You may want to just set it to 4000 or more. DECLARE @sql NVARCHAR(4000);
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I was slow on the draw - still typing while you submitted. +1
0 Likes 0 ·
Thanks KenJ and CirqueDe. I over looked the limitation.
0 Likes 0 ·
Well - to the slower typist go the spoils in this case ;)
0 Likes 0 ·
Looks like it's all been put to rights in the end.
0 Likes 0 ·
hahahaha too funny
0 Likes 0 ·
KenJ avatar image
KenJ answered
the problem isn't your text output, it is the variable declaration. You have declared the @sql variable as nvarchar(255) so you only get the first 255 characters of the query. Use something larger: `Declare @sql nvarchar(4000);` or `declare @sql nvarchar(max);`
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Also you can replace all those case statements with the simpler nullif([SP_NAME], 'NULL') which returns NULL if the column = 'NULL', or the column if it is not NULL
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.