Conversion failed when converting the varchar value '08SANJAY3001' to data type int

Question

1.00/5 (2 votes)

See more:

my query is

SQL

delete from TestId where ID between 6 and 10

table contain id like

VB

08SANJAY3001
08SANJAY3002
08SANJAY3003

plz tell me how to solve it

Posted 17-Sep-14 23:04pm

sanjaysgh

Updated 17-Sep-14 23:37pm

George Jonsson

v3

Add a Solution

Comments

hypermellow 18-Sep-14 5:20am

Can you post the table definition for TestID?
... maybe include some sample data??

sanjaysgh 18-Sep-14 5:23am

id like
08SANJAY3001
08SANJAY3002
08SANJAY3003

hypermellow 18-Sep-14 5:31am

So, if your ID column does not contain integer values ... why are you trying to delete rows with an ID value between 6 and 10?
What is it you trying to achieve?

George Jonsson 18-Sep-14 5:36am

Eh, what is your expected result from such a conversion?
Is it the last digit you want?

[no name] 19-Sep-14 3:56am

Buddy how can we compare string and integer? that error itself says a lot right?

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-09-17T23:49:00

Well... did you expect it to work?
When you do BETWEEN two integer values, SQL will assume that you want an integer comparison, not a strign comparison (and the sort order is different, so it's important). As a result, it tries to be helpful, and convert the column values to integer - and of course it fails, because there is non-numeric data in teh table. You can try converting your vlaues to string, but you will have to fill them with leading zeros:

SQL

DELETE FROM TestId WHERE id BETWEEN '06' AND '10'

Mehdi Gholam · Answer 2 · 2014-09-17T23:47:00

Solution 2

Read this : http://stackoverflow.com/questions/15977911/sql-server-converting-varchar-to-int[^]

Posted 17-Sep-14 23:47pm

Mehdi Gholam

Gauri Chodanker · Answer 3 · 2014-09-18T00:28:00

The format of id value as given in your problem statement is incorrect.

Still assuming the first two characters as the number you would like to search in the between clause, you could do the following:

SQL

delete from TestId where  cast(substring('08Sanjay2011',1,2) as int) between 6 and 10

But this will not work if your between ranges more than 99 as the substring is derived only for first 2 characters.

/\jmot · Answer 4 · 2014-09-17T23:46:00

Solution 1

Try..

C#

TRY_CONVERT(int,'08SANJAY3001')

Posted 17-Sep-14 23:46pm

/\jmot

Updated 17-Sep-14 23:47pm

Samatha Reddy G · Answer 5 · 2014-09-18T00:53:00

Solution 5

once you try with this..

DELETE FROM TestId WHERE id BETWEEN '08SANJAY3003' AND '08SANJAY3005'

Posted 18-Sep-14 0:53am

Samatha Reddy G