Query help desired! - 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)

Query help desired!

Author

Topic

jaybee
Yak Posting Veteran

72 Posts

Posted - 2008-03-25 : 18:09:35

Hi all,

The query below, as it stands, does a search for strings like 'Fema', that exist in the word 'Female', and returns 'female'. Now, if the word 'Acc' stands on it's own, I want to know how to return it as 'Accessories' in the result set. And yes, I've tried the obvious (When 'Acc' Then 'Accessories') but that returns a Null instead of 'Accessories'!!

Here's the query:

SELECT CategoryID, Category, CASE substring(category, 1, 4)

when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup

FROM dbo.InventoryCategory

Thanks all,

Jaybee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-25 : 18:44:18

Works fine for me:

declare @s varchar(10)

set @s = 'Acc'

select case when substring(@s, 1, 4) = 'Acc' then 'Accessories' end

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-25 : 19:25:18

You could try something like


SELECT CategoryID, Category, long
from dbo.InventoryCategory
inner join 
(
select 'Acc' as short, 'Accessories' as long union all
select 'fema', 'Female' union all
select 'male', 'Male' union all
select 'gift' , 'Female' 
)translate on (translate.short=substring(category, 1, len(translate.short) ))

but it's unclear why it isn't working.

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-25 : 22:27:37

In this query you are saying substring(category, 1, 4) but you have only three characters in 'Acc' which not going to work when you are matching 4 characters in string. You could just put else condition in case and write
Else
‘Accessories’
I hope this will help any query you are more then welcome

here is your query

SELECT CategoryID, Category,
CASE substring(category, 1, 4)
when 'fema' then 'female'
WHEN 'Male' THEN 'Male'
WHEN 'Gift' THEN 'Female'
else
‘Accessories’
END AS CategoryGroup

FROM dbo.InventoryCategory

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-26 : 00:30:54

quote:
Originally posted by QAZAFI

In this query you are saying substring(category, 1, 4) but you have only three characters in 'Acc' which not going to work when you are matching 4 characters in string.

Not true, see my example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources