IsNull Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IsNull Question

Author

Topic

Serge
Starting Member

44 Posts

Posted - 2007-11-12 : 09:10:23

Hi,

I need to implement a query that will give me list of client names and their aka's, however if aka field is empty i.e. null, instead I want to show the town where client is located.

Thanks a lot

I tried something like that, but it didn't work.

select client_name, isnull (client_aka, 'Unknown AKA')
from current_clients
where client_name like '% Smith'

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-12 : 09:18:36

Your sql syntax seems correct to me. What did you get back from it?

For the "instead show town" part, I think you need to use CASE function.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-12 : 09:21:55

change the town to the actual column name in your table

select client_name, isnull(client_aka, town)
from current_clients
where client_name like '% Smith'

KH
[spoiler]Time is always against us[/spoiler]

Serge
Starting Member

44 Posts

Posted - 2007-11-12 : 09:24:01

Ok, I just had a look at the DB table and client_aka is a "Not Null" field. However, users seem to put spaces in this field... So the question stays the same can I test for ZLS in select case?

i.e.

select client_name, IsZeroLengthString (LTRIM(RTRIM(client_aka)), 'Unknown AKA')
from current_clients
where client_name like '% Smith'

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 09:28:20

select client_name, CASE WHEN LTRIM(RTRIM(client_aka)) = '' THEN 'Unknown AKA' ELSE LTRIM(RTRIM(client_aka)) END
from current_clients
where client_name like '% Smith'

Serge
Starting Member

44 Posts

Posted - 2007-11-12 : 09:30:55

quote:
Originally posted by khtan

change the town to the actual column name in your table

select client_name, isnull(client_aka, town)
from current_clients
where client_name like '% Smith'

KH
[spoiler]Time is always against us[/spoiler]

I have tried that, it didn't work :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-12 : 09:40:54

post your table DDL, sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

Serge
Starting Member

44 Posts

Posted - 2007-11-12 : 10:33:29

Right I resolved it. I used an intermediate select statement the result of which I assigned to a variable @myresult and then had an if statement evaluating @myresult.
I don't know how efficient it really is but seems to work.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-12 : 18:51:11

You don't need to use a variable. Assuming, that there are "blank" values. Try the NULLIF function:

select client_name, COALESCE(NULLIF(RTRIM(LTRIM(client_aka)), ''), 'Unknown AKA')
from current_clients
where client_name like '% Smith'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-12 : 20:05:21

quote:
Originally posted by Serge

Right I resolved it. I used an intermediate select statement the result of which I assigned to a variable @myresult and then had an if statement evaluating @myresult.
I don't know how efficient it really is but seems to work.

Post your query. That doesn't sound correct

KH
[spoiler]Time is always against us[/spoiler]

Serge
Starting Member

44 Posts

Posted - 2007-11-19 : 10:48:45

Here is the solution to my problem:
Firstly I missed out to mention that client_aka was in a different table and I was using join to get that data. When instead I should have been using left join. Though both tables joined there was no matching fields. So by using left join I was able to use isnull function sucesfully!

Thanks a lot everyone!

My mistake :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources