CODEWITHSUNDEEP
sqlsql-server-2008
GijsA
GijsA

Reputation: 250

Using SUBSTRING with a column name

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?

Upvotes: 0

Views: 8309

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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

Upvotes: 1

Related Questions