Conversion failed when converting varchar to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Conversion failed when converting varchar to int

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-15 : 10:25:53

Can you please tell me i would like to bring in all int based only, prior to doing a cast how to make sure to just to pick right rows.

I am getting this error:
Conversion failed when converting the varchar value 'BBHX_000462895' to data type int.

SELECT COUNT(*) FROM Order_ENC where cast(cust_account as int) > 100 and facilityid > 0 and len(ltrim(rtrim(O_recordnumber))) > 1;

Thanks a lot for the helpful info.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-15 : 10:49:51

is character based and cannot be converted to an int. if the leading characters do not matter, you could strip them and then you would be fine.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-15 : 11:31:26

Is there a way to pick only int based rows.

Thank you.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-15 : 11:34:25

IF it is always the same leading characters , you could use a replace cast(REPLACE(cust_account,'BBHX_','') as int) > 100 and facilityid > 0 and len(ltrim(rtrim(O_recordnumber))) > 1;

if not you would have to find where the numbers start

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-15 : 11:40:06

If you are unsure what it may start with, but the number always comes after the _ and there are no , you could do the following

SELECT
cast(SUBSTRING(cust_account,CHARINDEX('_',cust_account) +1,LEN(cust_account)) as int) > 100 and facilityid > 0 and len(ltrim(rtrim(O_recordnumber))) > 1;

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-15 : 14:54:24

The underscore is not guaranteed, all i want is to eliminate all those rows which are not a numeric. if any alphabet is found then ignore those records.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 16:11:01

where cust_account not like '%[0-9]%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-16 : 05:33:52

SELECT COUNT(*) FROM Order_ENC where TRY_CONVERT(INT, cust_account) > 100 and facilityid > 0 and O_recordnumber > '';

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-16 : 12:42:00

thanks for this new funtion TRY_CONVERT. my sql server version is 2008 R2. is there any other way like regexp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-16 : 15:06:36

You posted this in the 2012 forum.

SELECT COUNT(*) FROM dbo.Order_ENC WHERE CASE WHEN cust_account LIKE '%[^0-9]%' THEN 0 ELSE cust_account END > 100 and facilityid > 0 and len(ltrim(O_recordnumber)) > 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:38:25