Solved

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

Posted on 2015-02-03
5
839 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:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Accepted Solution

by:
HuaMinChen earned 250 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 14

Assisted Solution

by:Lokesh B R
Lokesh B R earned 250 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 50

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 69

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:murbro
ID: 40595279
Thanks
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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, show how to shrink a transaction log file down to a reasonable size.

710 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