Replace and space with new value
-
I'm a T-SQL newbie. I have a table that contains columns with both <null> and space (blank) values. We'd like to replace these with "n/a". Does anyone have some code to do this?
SQL2K
Thanks
-
It must be done on presentation level, not in database.
_____________
Code for TallyGenerator -
update table
set field ='N/A'
where isnull(field,' ')=' '
-
As post 2 says, at the presentation level, don't update.
Using the principle of post 3:
SELECT (CASE where isnull(field,' ') = ' ' THEN 'N/A' ELSE field END) AS NewField
This should work, since if the field can contain spaces then it must be some sort of char or varchar or whatever.
Remember ISNULL will only work if the two elements have a common data type.
-
Thanks to you all. This worked exactly as needed.
-
Sometimes, there is no presentation layer...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Actually, there is always a presentation layer.
I know what you're thinking, Jeff, and for pure batch scenarios the 'presentation layer' is the receiving end of a SELECT statement. This may be a file, generated email, console window or whatever, but still it's the 'presentation layer' in this case. What Sergiy is suggesting is that you shouldn't implement the 'N/A' in the model, but at a higher level. (not necessarily to the letter what OSI refers to as presentation layer, though)
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply