Problems with Case Statement - 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

General SQL Server Forums

New to SQL Server Programming

Problems with Case Statement

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-14 : 12:01:48

I have the following Case statement:

select
case when cust_name is null or cust_name not like 'abc%' then null
else cust_name
end
from customer_base

This pulls only the names that are ' ' and names not like 'abc%', though it is not pulling any records for cust_name like 'abc%'. There are 10 records in the table which have cust_name like 'abc%'.

Any ideas?

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:06:37

so what is your expected output?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-14 : 12:17:27

quote:
Originally posted by Petronas

I have the following Case statement:

select
case when cust_name like 'abc%' then cust_name else null
end
from customer_base

This pulls only the names that are ' ' and names not like 'abc%', though it is not pulling any records for cust_name like 'abc%'. There are 10 records in the table which have cust_name like 'abc%'.

Any ideas?

Thanks,
Petronas

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-14 : 12:21:37

I need to see all the records that if
1. They have " " or if have name not starting with 'abc%' then in both cases it should show as " " (null)
also it should show
2. All the records that start with 'abc%' sometime like this:

Cust_name
" "
" " (even though in the databse this shows up as 'jfg%')
abc

Thanks for your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:26:11

do you mean this?


select 
case when nullif(cust_name,' ') is null or cust_name not like 'abc%' then null
else cust_name
end 
from customer_base

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-14 : 12:32:16

Thanks, for your prompt reply. I tried the above statement, but it still does not show me records having name like 'abc%'

Appreciate your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:35:29

are you sure the query i gave is exact one you're running without any alteration?

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-14 : 12:46:19

Thanks, for your time. It worked now. I copy pasted it this time around. Truly appreciate your help.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:47:23

Welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-15 : 01:45:38

Wont this work?

select
case when cust_name like 'abc%' then cust_name
else null
end
from customer_base

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-15 : 01:47:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114352

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources