Syntax error converting the varchar value. 1

[jjlbmcnews]

I'm struggling to get my head around this problem, I'm using T-SQL to extract a string of data from a field and then break it up so that I can insert it into other fields, everything is fine until I try and insert one value, '123,456'. As this value comes from a TEXT field my insert statement is unable to insert the value '123,456' into the field I require as the destination field has a data type of INT. Can anyone point me in the right direction? All the CAST and CONVERT options I've tried don't work. I've also noticed that when I take out the comma it works fine but I need to keep the comma in, all help greatly appreciated.

 

[rt63]

As your destination field is INT, your final input has to be INT data type only. Assuming that removing comma will solve the problem, you can code it as


insert into <destination_table>
(<destination_field>)
select convert(int,replace(<source_field>,',',''))
from <source_table>



RT

 

[SQLBill]

You say the destination field is required to be INT. Well okay, but a comma is not an INT 'character'. Only numbers (and decimal points) are INT. There are two solutions to your problem.

1. Take the comma out
2. Change the datatype for the destination field.

-SQLBill

 

[jjlbmcnews]

I have tried what RT suggested but I still get two error messages - Argument data type text is invalid for argument 1 of replace function and Argument data type varchar is invalid for argument 3 of convert function.

 

[MeanGreen]

Since your conversion column is TEXT datatype, you will have to convert it to a VARCHAR datatype. Try this:

insert into <destination_table>
(<destination_field>)
select convert(int,replace(convert(varchar(25),<source_field>),',',''))
from <source_table>


This is assuming that your text field is less then 25 characters in length.

Good Luck!

 

[jjlbmcnews]

Ok thanks, thats cured one error message but I still get the second - Argument data type varchar is invalid for argument 3 of convert function?

 

[MeanGreen]

Here was my exact select statement that worked:


select convert(int,replace(convert(varchar(25),<column>),',',''))
from <table>


Replace <column> with your column and <table> with your table and it should work. My guess is you have an extra comma for the convert function which means it is trying to apply a style to the output. Use the query above and see if you get the same error.

Good Luck.

 

[jjlbmcnews]

This is the query I have at the moment -

SELECT CONVERT(INT,REPLACE(CONVERT(VARCHAR(25),MyTable),',',''),SUBSTRING(Comment,CHARINDEX
('<Circulation>', MyColumn)+13,CHARINDEX('</Circulation>', MyColumn)-CHARINDEX('<Circulation>',MyColumn)-13))
FROM MyTable

I can't see what is wrong?

 

[MeanGreen]

Try this:

SELECT CONVERT(INT,REPLACE(CONVERT(VARCHAR(25),MyTable),',','')),SUBSTRING(Comment,CHARINDEX
('<Circulation>', MyColumn)+13,CHARINDEX('</Circulation>', MyColumn)-CHARINDEX('<Circulation>',MyColumn)-13))
FROM MyTable

I think you left off the closing parenthesis.

Good Luck.

 

[jjlbmcnews]

Much better, I have other unrelated problems now though! Thanks very much for your help.

 

[MeanGreen]

If you add the right parenthesis, it will get you your integer value. To verify that, comment out the stuff to the right beginning with the SUBSTRING and you will see that you are getting your values. Next you will need to work on your SUBSTRING column to correctly set it up.

Good Luck!

 

[jjlbmcnews]

Ok, spent sometime trying to set-up the SUBSTRING statement but not having much luck! Why, since I've included the multiple CONVERT functions, does the SUBSTRING function now become ineffective? It now returns the whole string rather than the section of the string that I require, any further advice would be much appreciated. Good news is that I get my INT value without the comma!