update NULL to Blank for Dynamic fileds

Question

update NULL to Blank for Dynamic fileds

adisql

SSCrazy

Points: 2893
More actions
April 27, 2021 at 3:26 pm

#3876357

Hi,
All the fields are dynamic in the table. I would like to update all the NULL values to blank in those fields.
Any inputs please.

Thanks.
May 5, 2021 at 5:04 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

What does 'blank' mean? Empty string? What about columns that are not strings? What do you mean by 'all fields are dynamic'? Tables have columns, not fields.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

adisql SSCrazy Points: 2893 More actions · Answer 2

I mean columns. Blank means Empty strings. All columns are strings.

adisql SSCrazy Points: 2893 More actions · Answer 3

adisql

SSCrazy

Points: 2893

April 27, 2021 at 4:21 pm

#3876391

example data.

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 4

Making a guess here, but is that a TABLE or a VIEW or STORED PROCEDURE? I am just trying to imagine designing a system that will alter a table to rename columns (or add columns) pre day. First, you are going to hit a wall when you max out on columns if you are adding them and if you renaming columns, you are losing data and any schema bound tools are not going to like metadata changing (SSIS for example).

Therefore, I expect that is a view or a stored procedure that is generating that data, or possibly Dynamic SQL creating a temp table or table variable with that information in it. Either way, it should be as simple as putting in a few ISNULL's to convert the NULL to an empty string.

Now if that REALLY is a table that is having the columns renamed/added on a daily basis, I would try to find a way to change that over to a view or stored procedure.

Another approach would be to change the source application so it doesn't pass NULL into the table, but passes an empty string. This will fix future data, but not current data.

Another approach would be to not muck around with the data. If it is NULL, leave it as NULL but change it when pulling the data from the database to the application layer via ISNULL or inside the application once the data is in memory.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.