Conversion Failure in SQL-CodePudding

While running the below query I'm met with the following error:

Conversion failed when converting the varchar value '0.' to data type int.

I understand that it sees the column value '0.' as numeric but cannot convert to an INT data type. I wanted to ask how I would work around this as I would only like to display the whole numbers in the column.

The query is as follows:

SELECT 
    wh.Id,
    CASE WHEN ISNUMERIC(wh.Uid AS INT) = 1 
    THEN CAST(UID AS INT) 
    ELSE 0
    END AS [Cardholder ID]
FROM  database.dbo.table wh

Some examples of the contents of the column are as follows:

112
222225
55546
4478
0.)
0.
C/Windows/system.ini
Text here

CodePudding user response：

Rather than isnumeric(), I tend to use try_convert().

That said, try_convert(money,...) seems to be a little more forgiving.

Example

Declare @users Table ([UID] varchar(50))  Insert Into @users Values 
 ('112')
,('222225')
,('55546')
,('4478')
,('0.)')
,('0.')
,('C/Windows/system.ini')
,('Text here')
,('4.')

 
Select * 
      ,NewValue = try_convert(int,coalesce(try_convert(money,UID),0))
from @users

Results

UID                     NewValue
112                     112
222225                  222225
55546                   55546
4478                    4478
0.)                     0
0.                      0
C/Windows/system.ini    0
Text here               0
4.                      4