Substring function generates error when I try to use (LEN(expression)-1) as length argument

Question

Post reply

Substring function generates error when I try to use (LEN(expression)-1) as length argument

Luis Figueroa

SSC-Addicted

Points: 414
More actions
May 24, 2010 at 11:54 am

#147234

Example:
Lets say exp1 = username and i am interested on getting rid of the last character of the name ex: usernam
I do realize that this can be easily done in a different way which I will show after my example. But for this arguments sake let's say i wanted to use the substring function. If I remove the "-1" the function works but it returns the whole string ofcourse.
SUBSTRING(exp1,1,LEN(exp1) - 1)
I also tried SUBSTRING(exp1,1,(LEN(exp1) - 1)) this caused the same generic error.
Am I missing something in the syntax? or is it that it is just not possible to calculate the argument at runtime from a simple mathematical operation.
an Alternate way to do this is
REVERSE(SUBSTRING(REVERSE(senderdomainname),2,20)) but it seems like it takes 2 additional operations than if we could just past Len(exp1)-1 as a parameter to the substring function.
Anyway, I must be missing something very simple here.. Please let me know if I have a syntax problem on my first approach.
Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 1

declare @exp1 varchar(32) = 'username'

select SUBSTRING(@exp1,1,(LEN(@exp1) - 1))

--returns 'usernam'

declare @exp1 varchar(32) = ' '

select LEN(@exp1)

--returns 0

select SUBSTRING(@exp1,1,(LEN(@exp1) - 1))

--Msg 537, Level 16, State 2, Line 4

--Invalid length parameter passed to the LEFT or SUBSTRING function.

--because 0-1 = -1 and that is invalid

so.... you will need to add a case statement to check for that condition.

select SUBSTRING(@exp1,1,(case LEN(@exp1) when 0 then 1 else LEN(@exp1) end) - 1)

or some such thing...

[font="Courier New"]ZenDada[/font]

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 2

As Zendada States you need to add a case statement bcz your syntax is correct but if its an Empty string it would have a len of -1 and returns an Error.. So you can try using an IF ELSE statement like

LEN(TRIM(expr1))>1 ? Substring(Expr1,1,LEN(Expr1)-1) : Expr1

Luis Figueroa SSC-Addicted Points: 414 More actions · Answer 3

I had to move on as I needed to make some progress on the ETL but I will try this when I get a chance and post the outcome of this approach.

Thank you,

Luis

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Cheat... no CASE required.

SELECT ISNULL(SUBSTRING(@exp1,1,(LEN(NULLIF(@exp1,'')) - 1)),'')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)