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

SQL Server 2008 Forums

Transact-SQL (2008)

Case Statement

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-13 : 21:49:48

I am extracting data from a table using SQL 2008. The particular field (WKSHT) is made up of 6 characters (e.g. A00000). I am using the substring function to extract the first two characters of this field to indicate what worksheet a particular record comes from.

However, that 6 digit code could be A00000 or B10000 or several others. What I would like to say in my sql statement is if the second digit of the worksheet code does not equal 0, the return it to me.

In the examples I provided - A00000 would return A and B10000 would return B1. I am not sure how to get this to work though. Can anyone help me? What I have so far is the following:

SELECT (substring([2552].[wksht], 1, 2), case when (substring([2552].[wksht], 2, 2) = 0 then '' else
(substring([2552].[wksht], 1, 2)) as WKSHT, [2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Please help!

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 09:26:06

Here's an example of using wildcards to do this:
create table #temp(myval varchar(20))

insert into #temp(myval)values('A00000'),('B10000')

select case when myval like '[A-Z][^0]%' then LEFT(myval,2) else LEFT(myval,1) END
from #temp

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 10:38:54

quote:
Originally posted by mfemenel

Here's an example of using wildcards to do this:
create table #temp(myval varchar(20))

insert into #temp(myval)values('A00000'),('B10000')

select case when myval like '[A-Z][^0]%' then LEFT(myval,2) else LEFT(myval,1) END
from #temp

Mike
"oh, that monkey is going to pay"

Thank you, Mike. I am a little confused though. I am just starting to learn SQL. Can you help me with this one. Here is my query so far:

SELECT (substring([2552].[wksht], 1, 2), case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else
(substring([2552].[wksht], 1, 2)) as WKSHT, [2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

I am not familiar with wild cards. How would I use them in this instance?

Thanks again, I appreciate the help!

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 12:11:09

hopefully I got the syntax right but something like this. I can't test mys statement obviously because I don't have your structure.
SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 12:22:33

quote:
Originally posted by mfemenel

hopefully I got the syntax right but something like this. I can't test mys statement obviously because I don't have your structure.
SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Mike
"oh, that monkey is going to pay"

Thanks, Mike.

I get this error -

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Can you tell me what that means?

JB

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 12:34:28

I was missing a comma at the end of my "END" statement for the case.
SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

I wanted to explain the wildcard characters to you as well so you get what's going on. We have a 2 character string we're getting from our Substring function what we're saying is [A-Z] if the first character is any letter a-z and the second character does not equal zero [^0] then the select statement should return the 2 characters from our substring function. Otherwise we know the second character was a 0 and so we return only 1 character from the substring function. You could also write that in reverse as [A-Z][0] which would give you your case for any first character that is alpha and is followed by a zero.

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 13:12:57

quote:
Originally posted by mfemenel

I was missing a comma at the end of my "END" statement for the case.
SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

I wanted to explain the wildcard characters to you as well so you get what's going on. We have a 2 character string we're getting from our Substring function what we're saying is [A-Z] if the first character is any letter a-z and the second character does not equal zero [^0] then the select statement should return the 2 characters from our substring function. Otherwise we know the second character was a 0 and so we return only 1 character from the substring function. You could also write that in reverse as [A-Z][0] which would give you your case for any first character that is alpha and is followed by a zero.

Mike
"oh, that monkey is going to pay"

Thanks, very much Mike. I am going to try it now and will let you know what happens......

Thanks again,

JB

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 13:21:55

quote:
Originally posted by jcb267

quote:
Originally posted by mfemenel

I was missing a comma at the end of my "END" statement for the case.
SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

I wanted to explain the wildcard characters to you as well so you get what's going on. We have a 2 character string we're getting from our Substring function what we're saying is [A-Z] if the first character is any letter a-z and the second character does not equal zero [^0] then the select statement should return the 2 characters from our substring function. Otherwise we know the second character was a 0 and so we return only 1 character from the substring function. You could also write that in reverse as [A-Z][0] which would give you your case for any first character that is alpha and is followed by a zero.

Mike
"oh, that monkey is going to pay"

Thanks, very much Mike. I am going to try it now and will let you know what happens......

Thanks again,

JB

I got this error message again:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Here is the code I am running:

SELECT (substring([2552].[wksht], 1, 2),
--case when (substring([2552].[wksht], 2, 2) = 0 then ' ' else (substring([2552].[wksht], 1, 2)) as WKSHT,
case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2 else ' ' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]

from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Can you tell what is wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-14 : 13:26:18

You are STILL fighting with this issue?

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 13:26:39

[code]SELECT substring([2552].[wksht], 1, 2),
Case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no][/code]

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 13:31:20

quote:
Originally posted by Peso

You are STILL fighting with this issue?

E 12°55'05.63"
N 56°04'39.26"

Yes, can you beleive it?!?!?! I desperately want to get it.....This will help me at work a lot. Instead of coming through 10 years worth of cost reports for every hospital in the USA, I will be able to focus on the 1 that I am working on!

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 18:19:36

quote:

Originally posted by sodeep

SELECT substring([2552].[wksht], 1, 2), 
Case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no], 
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n], 
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Thanks, Sodeep.

I ran this and got results. However, the results are not what I expected. When the worksheet code is A00000 the results display A0 and I want it to display only A. When the Worksheet code is B10000 the result displays B1 which is correct. Can you help me correct this?

Thanks!

JB

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 18:26:20

quote:
Originally posted by jcb267

quote:
Originally posted by sodeep

SELECT substring([2552].[wksht], 1, 2),
Case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Thanks, Sodeep.

I ran this and got results. However, the results are not what I expected. When the worksheet code is A00000 the results display A0 and I want it to display only A. When the Worksheet code is B10000 the result displays B1 which is correct. Can you help me correct this?

Thanks!

JB

I don't fully understand the like '[A-Z][^0]' part of the select statement, maybe that is my problem. Do you have any other siggestions?

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 18:49:15

Is that A0 (with a Zero) or AO with the letter 'O'?

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 18:52:09

quote:
Originally posted by mfemenel

Is that A0 (with a Zero) or AO with the letter 'O'?

Mike
"oh, that monkey is going to pay"

It is a zero, Mike. Sorry for not being clear on that!

JB

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 18:56:53

Ok, that's what I had it coded for. Go ahead and add this: substring([2552].[wksht],2,2) as one of the fields in your select statement. Let me know what comes back for one of the ones where you're gettin A0 back. I think our substring is getting something unexpected. Paste a handful of them back here. What I want to see is what we're getting back from the substring([2552].[wksht],2,2) statement.

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 19:28:42

quote:
Originally posted by mfemenel

Ok, that's what I had it coded for. Go ahead and add this: substring([2552].[wksht],2,2) as one of the fields in your select statement. Let me know what comes back for one of the ones where you're gettin A0 back. I think our substring is getting something unexpected. Paste a handful of them back here. What I want to see is what we're getting back from the substring([2552].[wksht],2,2) statement.

Mike
"oh, that monkey is going to pay"

OK, trying it now. I will past the entire query back for you to look at.

Thanks,

JB

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 19:32:42

quote:
Originally posted by jcb267

quote:
Originally posted by mfemenel

Ok, that's what I had it coded for. Go ahead and add this: substring([2552].[wksht],2,2) as one of the fields in your select statement. Let me know what comes back for one of the ones where you're gettin A0 back. I think our substring is getting something unexpected. Paste a handful of them back here. What I want to see is what we're getting back from the substring([2552].[wksht],2,2) statement.

Mike
"oh, that monkey is going to pay"

OK, trying it now. I will past the entire query back for you to look at.

Thanks,

JB

OK, I am still getting the A when 2552.wksht code is A00000 and just B when 2552.wksht code is B10000. Here is the code:

SELECT substring([2552].[wksht], 1, 1),
Case when substring([2552].[wksht],2,2) like '[A-Z][^0]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 19:39:23

My apologies. I had the substring wrong when evaluating. Try this:

SELECT substring([2552].[wksht], 1, 1),
Case when substring([2552].[wksht],1,2) like '[A-Z][1-9]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Mike
"oh, that monkey is going to pay"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 19:50:12

quote:
Originally posted by mfemenel

My apologies. I had the substring wrong when evaluating. Try this:

SELECT substring([2552].[wksht], 1, 1),
Case when substring([2552].[wksht],1,2) like '[A-Z][1-9]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Mike
"oh, that monkey is going to pay"

Thanks very much, I am trying it now and will let you know how I make out. I am trying to teach myself SQL, what would you recommend?

JB

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 19:52:13

quote:
Originally posted by jcb267

quote:
Originally posted by mfemenel

My apologies. I had the substring wrong when evaluating. Try this:

SELECT substring([2552].[wksht], 1, 1),
Case when substring([2552].[wksht],1,2) like '[A-Z][1-9]' then substring([2552].[wksht], 1, 2) else '' END,
[2552].[wksht], [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no],
[hpi].[Hosp_Name], [hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT], [hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

Mike
"oh, that monkey is going to pay"

Thanks very much, I am trying it now and will let you know how I make out. I am trying to teach myself SQL, what would you recommend?

JB

That worked, thanks very much! I really appreciate the help......

JB

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources