Len and If Statement help - 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

General SQL Server Forums

New to SQL Server Programming

Len and If Statement help

Author

Topic

Ethan135
Starting Member

3 Posts

Posted - 2013-01-10 : 08:02:06

Hi everyone. I'm new here and also new to SQL. I'm trying to set up a situation where I take off the last two characters of all the values in my 'objectname' field. But if the objectname field is 'report123' or 'report321' then I want it to remove the last 3 characters. I keep getting a syntax error on the If statement. I'm not sure what im doing wrong.

UPPER(LEFT(objectname,IF(objectname = 'Report123' AND 'Report321', len(objectname)-3,len(objectname)-2))) AS Objectname,

thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-10 : 08:06:45

Use a case expression like shown below:

UPPER(LEFT(objectname,
	CASE 
		WHEN objectname = 'Report123' OR objectname = 'Report321' 
			THEN LEN(objectname)-3
		ELSE LEN(objectname)-2
	END ))

If you have many such special cases, you can use the IN clause as in objectname in ('Report123','Report321') instead of individual equality expressions and OR operator.

Ethan135
Starting Member

3 Posts

Posted - 2013-01-10 : 08:22:31

quote:
Originally posted by sunitabeck

Use a case expression like shown below:
UPPER(LEFT(objectname,
CASE
WHEN objectname = 'Report123' OR objectname = 'Report321'
THEN LEN(objectname)-3
ELSE LEN(objectname)-2
END ))
If you have many such special cases, you can use the IN clause as in objectname in ('Report123','Report321') instead of individual equality expressions and OR operator.

That works perfectly. Can you explain why an IF isn't used here?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 08:59:46

An if statement is a control of flow instruction. It doesn't return a value.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ethan135
Starting Member

3 Posts

Posted - 2013-01-10 : 09:00:18

quote:
Originally posted by nigelrivett

An if statement is a control of flow instruction. It doesn't return a value.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thank you for your help

RajanThan
Starting Member

7 Posts

Posted - 2013-01-11 : 00:17:42

select

LEFT(objectname,LEN(objectname)-3) from tablename

Rajan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 00:20:56

quote:
Originally posted by RajanThan

select

LEFT(objectname,LEN(objectname)-3) from tablename

Rajan

what about conditional logic OP explained?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shan007
Starting Member

17 Posts

Posted - 2013-01-11 : 03:50:59

If cannot be used in the way as you used, If can validate boolean expression, it would validate True or False, according to the state we've to form the code. Your script with IF statement can be written as follows:

IF @objectname='Report123' or @objectname='Report321'
select UPPER(Left(@objectname,len(@objectname)-3))
else
select UPPER(left(@objectname,LEN(@objectname)-2))

this is not a best practice for this case, as it has dup code, but still i'd like to explain how IF statement can be used.

==============================
I'm here to learn new things everyday..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources