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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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;
Go to Top of Page

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.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 16:11:01
where cust_account not like '%[0-9]%'
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:38:25
quote:
Originally posted by gbritton

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



Should that be

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

? which will just select rows where the cust-account only contains digits

Slightly more complicated if the O/P also wants to include "-1234"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-19 : 03:15:30
"-1234" is less than zero anyway... :-)


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -