Converting IIF in Access Query to SQL Server

[Robin Hammond]

I am trying to upsize a database to SQL server (on which I am a novice). In
Access as part of a much more complex query I had the following (from sql
view)

SELECT
IIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemNa
me]) AS ShortName FROM corp_info

Which gives a return value for the whole of ItemName if there is no star in
it, or the portion up to the star if there is a star

I am having a nightmare trying to get an equivalent in SQL server. I've
worked out that Instr is charindex in sql and can adjust for that, but can't
work out how to get a conditional select statement working.

It may well be obvious, but any help much appreciated. Thanks.

Robin Hammond
www.enhanceddatasystems.com

[Robin Hammond]

I found a solution by looking at a post below using CASE that hadn't been
archived yet, hence not showing up on Google.

Thanks anyway.
"Robin Hammond" <rj********@PLEASEnetvigator.com> wrote in message
news:bn*********@imsp212.netvigator.com...

I am trying to upsize a database to SQL server (on which I am a novice). In Access as part of a much more complex query I had the following (from sql
view)

SELECT
IIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemNa me]) AS ShortName FROM corp_info

Which gives a return value for the whole of ItemName if there is no star in it, or the portion up to the star if there is a star

I am having a nightmare trying to get an equivalent in SQL server. I've
worked out that Instr is charindex in sql and can adjust for that, but can't work out how to get a conditional select statement working.

It may well be obvious, but any help much appreciated. Thanks.

Robin Hammond
www.enhanceddatasystems.com