IF Statement to select Right Text

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 2000 Forums

Transact-SQL (2000)

IF Statement to select Right Text

Author

Topic

mauddib
Starting Member

14 Posts

Posted - 2005-04-22 : 09:43:40

I need to select a text field as follows:

SELECT No_
FROM dbo.[Live Data$Item]

The Items returned Are of AlphaNumberic type, starting with two letters and having 4-7 numbers after this. For example: FA0123

However some of the items WILL for internal reasons start with NPI_ before the item number, such as NPI_FA0123.

How can I use an IF Statement to say IF item starts with NPI_ then dont return the NPI_ part, just the rest of it?

GmcB

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-22 : 09:49:55

[code]
SELECT CASE WHEN No_ like 'NPI%' then right(No_,(len(no_) -3)) else No_ END As No_
FROM dbo.[Live Data$Item]
[/code]

Though better might be to redesign the table and split the NPI bit out.

-------
Moo. :)