Click here to Skip to main content
15,903,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All

I have a table with a column that is a CHAR(10) which is used to hold a 'reference'.
If a record is created automatically by the system then the reference is pre-defined and is always an integer but there are manually (user) created records which can have any value in them.
I need to JOIN on this column but only when it contains an integer...

Sounds easy but I cannot seem to get it to work.

The closest I got was
CAST( CASE WHEN ISNUMERIC(Ref_Column) = 1 THEN Ref_Column ELSE 0 END as INT)


However ISNUMERIC allows currency symbols so it fails when the Ref_Column contains something like '£123.45 ' with the error
SQL
Conversion failed when converting the varchar value '£123.45  ' to data type int.



Anyone got any slick solutions for this?

SQL 2008 so no PARSE option...

Thanks
Posted
Updated 15-Sep-15 23:40pm
v3

1 solution

What about first converting it to money?
SQL
declare @value  varchar(100) = '$125'
select case when isnumeric(@value) = 1 then cast(cast(@value as money) as int) else 0 end
 
Share this answer
 
Comments
[no name] 16-Sep-15 6:44am    
Nice, a 5.
dnibbo 16-Sep-15 7:50am    
Thanks very much Tomas, works perfectly.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900