If then else - 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)

If then else

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 11:52:07

The field 2552.wksht represents a Medicare hospital cost report
worksheet. It is formatted as nvarchar (255) but is always 6
characters in length, like A00000 or B10000. What I want this query
to do is to return the first character to me and if the second
character IS NOT =0, then return that to me too, otherwise return
nothing in the second position. For example, if the WSKSHT code is
A00000 then I want A in my results. If the WKSHT code is B10000 then
I want B1 in my results.

Here is my query so far:

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

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

Please Help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-15 : 08:55:48

Do it like this:


select
case
  when substring([2552].[wksht], 2, 1) = '0' then left([2552].[wksht],1)
  else left([2552].[wksht],2)
end as yourColumnName,
...
from ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-15 : 17:51:36

quote:
Originally posted by webfred

Do it like this:

select
case
when substring([2552].[wksht], 2, 1) = '0' then left([2552].[wksht],1)
else left([2552].[wksht],2)
end as yourColumnName,
...
from ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks, Webfred. I appreciate the help......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 14:12:14

SELECT REPLACE(LEFT([2552].[wksht],2),'0','') FROM...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources