January 15, 2008 at 9:11 am
In SQL Server 2000 queries, is there a way to replace results if a field happens to be empty?
For example, if I have the following query:
SELECT A.name, A.age FROM names A
ORDER BY name
Can I have some type of logic in the query that would replace any empty name fields with the word 'UNKNOWN'?
Thanks!
January 15, 2008 at 9:19 am
You should look into using either a case statement or the coalesce function.
January 15, 2008 at 10:00 am
Also, you can use the ISNULL() function.
January 15, 2008 at 10:06 am
Would IsNull work for empty fields or just NULL fields?
Would something like this work?
CASE name
WHEN '' THEN 'UNKNOWN'
ELSE name
END AS Name
January 15, 2008 at 10:12 am
ISNULL() does only work for NULL fields and does not catch empty strings. Your example should work just fine. I use something similar in a lot of my queries.
January 17, 2008 at 4:39 am
This one would catch both nulls and empty strings, I think.
[font="Courier New"]
SELECT CASE WHEN COALESCE(A.NAME, '') = '' THEN 'unknown'
ELSE A.NAME
END,
A.age
FROM names A
ORDER BY CASE WHEN COALESCE(A.NAME, '') = '' THEN 'unknown'
ELSE A.NAME
END[/font]
(formatted using the Simple-Talk prettifier!)
Best wishes,
Phil Factor
January 25, 2008 at 9:15 am
Phil you just made me so happy;
I had previously bookmarked http://www.simple-talk.com/prettifier/default.php,
which is fine for formatting code for some things, but not for here at SSC.
the new link you posted at http://extras.sqlservercentral.com/prettifier/prettifier.aspx is fantastic.
Thank you, Thank you!
Lowell
January 25, 2008 at 10:17 am
Lowell,
Thanks so much for noticing. Yes, I wrote the IFCODE version just because I got so damned frustrated with posting SQL code into the new forum software (nothing anyone could do about that, I'm afraid).
The SQLServerCentral prettifier is a well-kept secret, for some reason. I use it for pasting nicely colour-coded SQL into the forums.
http://extras.sqlservercentral.com/prettifier/prettifier.aspx?style=5
If you prefer the Simple-Talk version, (different logo and colours)
http://extras.sqlservercentral.com/prettifier/prettifier.aspx?style=5&skin=st
Evidently, the forum developers eventually decided there was too much of a security risk in allowing the pasting of rich text into the editing screens, so they introduced these pesky IF codes. I'm not much impressed with them, especially the code blocks, so I altered the Prettifier to put them in to SQL text. Much nicer.
I'm working on another project for SQLServerCentral that takes this to the max. It is a Wiki which has all the IF codes, but also a [SQL] [/SQL] tag that does all the formatting for you. I've even done an IFCode parser written as a stored procedure. We're just applying the paint to the application at the moment.
Best wishes,
Phil Factor
January 25, 2008 at 10:37 am
If you don't want to use a Case statement, use:
coalesce(nullif(name, ''), 'Unknown')
The nullif will make the name null if name = ''.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply