Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-02-03
5
Medium Priority
?
1,223 Views
Last Modified: 2015-02-06
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
0
Comment
Question by:Murray Brown
5 Comments
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 1000 total points
ID: 40585716
Try
Select * From MARA_MBEW Where SubString(cast([Total Stock] as varchar),1, 1) = 2

Open in new window

0
 
LVL 15

Assisted Solution

by:Lokesh B R
Lokesh B R earned 1000 total points
ID: 40585736
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40586232
Use LEFT function:
Select * From MARA_MBEW Where LEFT([Total Stock],1) ='2'

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40586472
Lokesh is correct, the best style for performance is:

Where [Total Stock] Like '2%'
0
 

Author Closing Comment

by:Murray Brown
ID: 40595279
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question