Case function - 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)

Case function

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-09 : 06:38:32

hi

i got following data

ABC_COS
ABC_COS
DEF-00
DEF-00
ZER-00
JFK_COS

i want in a case fucntion following

when i got 2 decimals, like DEF-00
i want DE00, so the F gone and '-'
(i tried with stuff(substring) but then i only can or remove the '-' or the 'F', i need both to be gone.
and when i got JFK_COS or ABC_COS i want that to stay.
so i need to filter on that or something :/.

Ty

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-09 : 06:42:00

You could use the REPLACE function.

e.g.
REPLACE(field_name,'F-','')

which looks for the characters 'F-' and strips them out.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 07:21:50

try this one

select case isnumeric(right(column1,2)) when 1 then left(column1,charindex('-',column1)-2)+right(column1,2)
else column1 end as column1 from tbale

Karthik

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-09 : 07:32:20

select data,case when data like '%_00' then stuff(data,3,2,'') else data end from
(
select 'ABC_COS' as data union all
select 'ABC_COS' union all
select 'DEF-00' union all
select 'DEF-00' union all
select 'ZER-00' union all
select 'JFK_COS'
) as t

Madhivanan

Failing to plan is Planning to fail

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-09 : 07:37:33

Hi,

You can use the CASE directly with the query, something like this

select case name
when 'DEF-00' THEN 'DE00'
else name
end as Name
from table

Regards
Thiyagarajan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-09 : 08:23:38

quote:
Originally posted by thiyagu_rind

Hi,

You can use the CASE directly with the query, something like this

select case name
when 'DEF-00' THEN 'DE00'
else name
end as Name
from table

Regards
Thiyagarajan

Thats not generalised

Madhivanan

Failing to plan is Planning to fail

Kokkula
Starting Member

41 Posts

Posted - 2009-04-13 : 05:46:41

Hello,

Adding more generalized by not mentioning the locations, instead of it getting the exact locations from the string

select data
,case when data like '%_00' then stuff(data,CHARINDEX('-',Data,1)-1,2,'') else data end from
(
select 'ABC_COS' as data union all
select 'ABC_COS' union all
select 'DEF-00' union all
select 'DEF-00' union all
select 'ZERF-00' union all
select 'ZER-00' union all
select 'JFK_COS'
) as t

Hope helpful...

Thanks,
Pavan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources