Error converting data type varchar to float in assigned column

0
votes

Hey I am trying to convert the data type from varchar to float of the column name 'Identification'. Below are the queries that I have created and both are throwing the error

Select distinct Convert(float,(Replace([Identification], '-','')))  FROM [A]
where identification is NOT Null;


Select Cast(Replace([Identification], '-','') AS FLOAT) FROM [A]
where identification is NOT Null;

Error Msg:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

2
sqlsql-serversql-server-2008
The error is telling you the problem, what about the error don't you understand and we can try to elaborate. Also, SQL Server 2008 has been out of support for over a year now, and you should really be looking at upgrades. Because you're using an unsupported you don't even have access to TRY_CAST/TRY_CONVERT, which really hinders fixing your data. - Larnu
I know what the problem is. But I am struggling hard to fix the issue. Can you tell me how can I change the datatype? Also I have used the Cast and Convert function but it's not working. Check my responses above - Xaaz
You have bad data in your column. This is just one reason why storing numerical data in (n)varchar is a bad idea, and you need to fix your design. Unfortunately, like I mentioned, as you're using unsupported software you can't use TRY_CONVERT; personally I would suggest you really look at those upgrades now. - Larnu
I can't upgrade because I don't have access. That's a DBA job. I need to sort out the solution into my domain - Xaaz

2 Answers

0
votes
Haaaz, try this:

   CASE WHEN ISNUMERIC([Identification]) THEN CONVERT(float, 
    [Identification])
0
votes
select cast('' as xml).value('xs:float(sql:column("val"))', 'float')
from (values ('a'),('b'), ('-123'))  v(val);



CREATE TABLE ATest(Identification varchar(100));

INSERT INTO ATest(Identification) 
VALUES('A'),('B'), ('A-B-123'), ('123-456'), ('1-1-1-1-1'), 
('123-E10'), /*you might need to handle this type of values when scientific notation should NOT be converted to float...
....eg. just replace E with another letter*/
('------1------')
;

SELECT
    Identification, ReplacedIdentification, 
    cast('' as xml).value('xs:float(sql:column("src.ReplacedIdentification"))', 'float') as ToFloatOrNull
FROM
(
Select [Identification], Replace([Identification], '-','') ReplacedIdentification
FROM [ATest]
WHERE[Identification] IS NOT Null
) as src
--use this to find Identification values which cannot be converted to float
--WHERE cast('' as xml).value('xs:float(sql:column("src.ReplacedIdentification"))', 'float') IS NULL;

DROP TABLE Atest;
GO