• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1142
  • Last Modified:

text to integer

Error. Operand type clash: text is incompatible with int . how can I convert text to integer in my query?Sql server
0
jaws1021
Asked:
jaws1021
  • 12
  • 9
  • 5
  • +3
1 Solution
 
hongjunCommented:
try this

cast(yourtextfield as int)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first, is it TEXT or VARCHAR/NVARCHAR?
note: in sql server 2005, do NOT use TEXT, but VARCHAR(MAX) instead.
also, in general, text means up to 2GB of data, but limited functionality.

now, the error means that you are trying to do
textcol + intcol
and sql server tries to convert the textcol into int, which is not allowed.

you need to force:

select cast(textcol as varchar) + cast(intcol as varchar)
0
 
JimBrandleyCommented:
angelIII - Won't your:
select cast(textcol as varchar) + cast(intcol as varchar)
 just concatenate the two strings?

Shouldn't it be this?
select cast(textcol as int) + intcol

Jim

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Scott PletcherSenior DBACommented:
>> how can I convert text to integer in my query? <<

CAST(CAST(textColumn AS VARCHAR(10)) AS INT)

NOTE: That works if the int value is the only value in the text.  If there could be other text after, say, a space, you can do this:

CAST(LEFT(CAST(@textcolumn AS VARCHAR(10)), CHARINDEX(' ', CAST(@textcolumn AS VARCHAR(10))))  AS INT)

Also, you need to make sure that the string is valid to be converted to an INT.  You can do that like so:

WHERE PATINDEX('%[^0123456789]%', LEFT(CAST(@textColumn AS VARCHAR(10)), CHARINDEX(' ', CAST(@textColumn AS VARCHAR(10))) - 1)) = 0

That will skip any textColumn values that won't convert successfully to an INT.
0
 
jaws1021Author Commented:
my column is text for custname and custid is integer

I say custname = custid it gives me that above error. So, should I use  your last code Scott?
0
 
Scott PletcherSenior DBACommented:
Hmm, looks like you are going from integer to text ... that's much easier :-) .

custname = CAST(custid AS VARCHAR(10))
0
 
JimBrandleyCommented:
jaws1021- Please post your SQL - there is some confusion here about exactly what you are trying to do. Are you trying to convert an integer (custid) to a string and store it in custname?

If so, ScottPletcher's post is correct. However, in your question, it says:
"how can I convert text to integer in my query?"

Jim
0
 
jaws1021Author Commented:
I put this and now get this error
CAST(CAST(f.custname AS VARCHAR(10)) AS INT) = m.custid

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'Documentat' to data type int.
0
 
jaws1021Author Commented:
custname is text
custid is integer

custname = custid  
0
 
Scott PletcherSenior DBACommented:
Sorry, I misunderstood earlier, *don't* cast custname at all, *instead* cast custid:

f.custname = CAST(m.custid AS VARCHAR(10))
0
 
dbbishopCommented:
I don't see how you can do it at all. You are trying to convert the character string 'Documentat' to an integer. It cannot be done.
0
 
jaws1021Author Commented:
gave me this error
Msg 402, Level 16, State 1, Line 3
The data types text and varchar are incompatible in the equal to operator.
0
 
JimBrandleyCommented:
f.custname = CAST(m.custid AS text)
0
 
jaws1021Author Commented:
nope not worked

Msg 529, Level 16, State 2, Line 3
Explicit conversion from data type int to text is not allowed.
0
 
JimBrandleyCommented:
Then try this:
f.custname = CAST(CAST(m.custid AS VARCHAR(10)), AS text)
0
 
Scott PletcherSenior DBACommented:
Sorry, I thought SQL could automatically convert varchar to text:

f.custname = CAST(CAST(m.custid AS VARCHAR(10)) AS TEXT)
0
 
jaws1021Author Commented:
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'AS'.
0
 
jaws1021Author Commented:
Msg 402, Level 16, State 1, Line 3
The data types text and text are incompatible in the equal to operator.
0
 
JimBrandleyCommented:
Are you trying to do a comparison or an assignment?
0
 
jaws1021Author Commented:
I am trying to inner join f.custname to m.custid
0
 
JimBrandleyCommented:
The you could try this one:
WHERE CAST(f.custname as VARCHAR(10)) = CAST(m.custid AS VARCHAR(10))

0
 
jaws1021Author Commented:
I think now okay, but why didn't we cast custname to integer since custid was integer? We did cast both to varchar, also varchar 10 is enough I don't know?
0
 
jaws1021Author Commented:
inner Join money m on
(
CAST(f.custname as VARCHAR(10)) = CAST(m.custid AS VARCHAR(10))
)
where ....
did that way
0
 
JimBrandleyCommented:
In the future, I would be wary of TEXT columns. They are deprecated in SQL Server 2005.
0
 
jaws1021Author Commented:
I wish it would be my choice..

what do you think about why didn't we cast custname to integer since custid was integer? We did cast both to varchar, also varchar 10 is enough I don't know?
0
 
JimBrandleyCommented:
Check the MAX(custid) and it will tell you how big the varchar needs to be.
0
 
jaws1021Author Commented:
thank you all!!
0
 
JimBrandleyCommented:
My pleasure. Good luck!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I think now okay, but why didn't we cast custname to integer since custid was integer?
2 remarks on that:
* because if you tried to cast a value to integer that is not, you will run into a conversion error
* how comes you join on 2 fields named custname and custid???? sounds like a design or at least naming error?!!!

>We did cast both to varchar, also varchar 10 is enough I don't know?
depends on how large custid can be. varchar10 means 10 digits ie up to 9.999.999.999, so 10million-1 customers...
0
 
Scott PletcherSenior DBACommented:
I picked 10 characters because you said the numeric value was an integer -- an integer cannot hold more than a 10-digit value (max value is specifically 2,147,483,647).
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 12
  • 9
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now