jimshock
asked on
Need to update blank fields to null
Basically I need the opposite of the solution in this post:
https://www.experts-exchange.com/questions/22129219/Replace-null-with-blank.html?sfQueryTermInfo=1+blank+field+null+updat
I need to write a SQL statement that will update all blank fields in all columns of a table to Null, and in fields that are Not Null eliminate anything more than a single blank space (this will account for space between a name, ie, "Microsoft Office") -- can anyone help?
https://www.experts-exchange.com/questions/22129219/Replace-null-with-blank.html?sfQueryTermInfo=1+blank+field+null+updat
I need to write a SQL statement that will update all blank fields in all columns of a table to Null, and in fields that are Not Null eliminate anything more than a single blank space (this will account for space between a name, ie, "Microsoft Office") -- can anyone help?
Make a backup first, then give this a whirl...
UPDATE YourTableName
SET YourColumnName = NULL
WHERE Len(LTRIM(RTRIM(COALESCE(Y
UPDATE YourTableName
SET YourColumnName = NULL
WHERE Len(LTRIM(RTRIM(COALESCE(Y
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I apologize for not getting back sooner. My question on these is, since I want the statement to go across all fields in the table, what do I set YourColumn to? Or do I have to do each column one at a time?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Understood. The "blanks to Null" part of your SQL works, but I've still got blanks spaces after fields with text in them...
ASKER
No biggie, I'm just going to use NeoTeq's second statement for the blank spaces after fields and yours for the blanks to Null. I really appreciate both you guys help!!
Thanks for the split. Good luck with your project. -Jim
ASKER