ascii character query - 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)

ascii character query

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-03-18 : 09:02:27

item custclass totalcustclass
-------------------------
06-5841 INST-CLINPRAC 1
06-5841 INST-MKT/MEDIA 2
06-5841 PROGRAM 1
06-5841 STANDARD 4
06-5845 STANDARD 1
AX-048 INST-MKT/MEDIA 4
KT-048 PROGRAM 2
KT-048 STANDARD 4

i want condition like if item is starting with number then totalcustclass whcih is count(*) remain same giving correct results...but if item startign with ascii character then totalcustclass r getting double so i have to /2..

i want results:

item custclass totalcustclass
-------------------------
06-5841 INST-CLINPRAC 1
06-5841 INST-MKT/MEDIA 2
06-5841 PROGRAM 1
06-5841 STANDARD 4
06-5845 STANDARD 1
AX-048 INST-MKT/MEDIA 2
KT-048 PROGRAM 1
KT-048 STANDARD 2

select
item, custclass,

case when item is <ascii> then count(custclass)/2
else count(custclass)
as totalcustclass

from itemcustclass

can anyone tell me what condition will come in case?

thanks for help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 09:11:31

CASE WHEN LEFT(@string,2) LIKE '[0-9][0-9]' THEN custclass ELSE custclass/2 END

Jim

mkool
Starting Member

25 Posts

Posted - 2008-03-18 : 09:15:55

thanks a ton jim.
got correct output.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources