Using SUBSTRING with a column name

Question

I've been trying this all morning and can't find what I'm doing wrong. I have a string, 'XXYY0000' and I need the XX and YY seperatly.

So this works and gets the XX values:

SELECT LEFT(l.LOAN_MATURITY, 2)
FROM LOAN As l

But why is this not working when I need to get the YY values?

SELECT SUBSTRING(l.LOAN_MATURITY, 3, 2)
FROM LOAN As l

I researched and it should give the same output as the MID() function, but it gives me this error.

Msg 8116, Level 16, State 1, Line 1

Argument data type numeric is invalid for argument 1 of substring function.

Why is the data type important for the SUBSTRING function but not the LEFT function?

Why it doesn't work I can't tell but it works when you cast the numeric to varchar: SELECT SUBSTRING(cast(l.LOAN_MATURITY as varchar), 3, 2) FROM LOAN As l — Luc
– Luc, Commented Oct 1, 2015 at 12:23

Lukasz Szozda · Accepted Answer · 2015-10-01 12:28:54Z

1

LEFT:

LEFT ( character_expression , integer_expression )

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

SUBSTRING:

SUBSTRING ( expression ,start , length )

expression

Is a character, binary, text, ntext, or image expression.

With LEFT there is implicit CAST, with SUBSTRING not. You need to CAST it manually:

SELECT SUBSTRING(CAST(l.LOAN_MATURITY AS VARCHAR(100)), 3, 2)
FROM LOAN As l

edited Oct 1, 2015 at 12:28

answered Oct 1, 2015 at 12:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SUBSTRING with a column name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related