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

Hi

I am trying to list all records where the number starts with a 2 but I get the error

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

I know that the following syntax doesn't work. What would the correct syntax be?

Select * From MARA_MBEW Where SubString([Total Stock],1, 1) = 2
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
HuaMin ChenSystem AnalystCommented:
Try
Select * From MARA_MBEW Where SubString(cast([Total Stock] as varchar),1, 1) = 2

Open in new window

0
 
Lokesh B RDeveloperCommented:
Hi,

Your column [Total Stock] is of type decimal.
SQL Function SUBSTRING(expression, start, length)  as Parameters where expression is of type VARCHAR
So you need to CAST/CONVERT [Total Stock] as VARCHAR.

Select * From MARA_MBEW Where SUBSTRING(CAST([Total Stock] AS VARCHAR),1, 1) = 2

Open in new window


You can try this to get all the records starts with 2
 Select * From MARA_MBEW Where [Total Stock] LIKE '2%'  

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use LEFT function:
Select * From MARA_MBEW Where LEFT([Total Stock],1) ='2'

Open in new window

0
 
Scott PletcherSenior DBACommented:
Lokesh is correct, the best style for performance is:

Where [Total Stock] Like '2%'
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.