Need to remove character from string if statement

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)

Need to remove character from string if statement

Author

Topic

Dovinshka
Starting Member

6 Posts

Posted - 2009-09-14 : 04:39:23

Hi There,

I'm trying to be able to create an IF clause where if a character has the number 8 or 9 at the start of the string (text), that number is removed from the string in the query.

As an example below -

SELECT T0.NumAtCard "Cust PO Number", T0.CardName "Customer Name", T0.DocNum as "Document No.", T1.Model as "Model", 
T1.U_SerialNo as "Serial No.", substring(T1.Sheets,3,1) as "Sheets", 
left(T1.Sheets,2)+'0' as "Size of Sheet", etc etc

Where T1.Sheets,2)+'0' give me the number 200 for example. However, if there is a number 8 or 9 at the start of the string, I need to be able to format the string to remove that number from the statement.

Any suggestions?

Thanks in advance, Dave.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-14 : 04:46:11

use case statement in select statement

Dovinshka
Starting Member

6 Posts

Posted - 2009-09-14 : 05:14:56

Ok, how could I use the CASE statement to remove the character from the string?

Dovinshka
Starting Member

6 Posts

Posted - 2009-09-14 : 10:55:39

No need I worked out the following:

CASE
WHEN LEFT(T1.Sheets,1) = '8' THEN SUBSTRING(T1.Sheets,2,2)+'0'
WHEN LEFT(T1.Sheets,1) = '9' THEN SUBSTRING(T1.Sheets,2,2)+'0'
ELSE
left(T1.Sheets,2)+'0' END AS "Size of Sheet"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources