I'm trying to compare 2 varchar columns- col_1 & col_2,
col_1
A
A
A
1.0
2.0
col_2
A
A
A
1
2
I need to make sure that 1.0 & 1 are equal values, I can't convert them to integers as there could be valid values like 1.5...
Any tips?
Casting to float does not convert to 1.0, its still stored as 1,
select cast(v as float)from
(select 1 as v) tb1
Does this solve your issue ?
Select CASE isnumeric(col_1)
WHEN 1 THEN CAST(CAST(col_1 as DECIMAL(20,10)) as varchar(50))
ELSE col_1
END
When you join with col_2 apply the same treatment to col_2. In your example 'A' will stay as 'A', and 1 will be '1.0000000000' in both columns.
ISNUMERIC; it is true for monetary amounts like '$1.00'.
Try something like this:
SELECT 'YES'
FROM sys.tables
WHERE CAST(1 as float) = 1.0
The CAST statement can be used to temporarily cast your Integer to be a Float.
You can't convert to integer, but you can convert to float. If you still for some reason want to compare with decimal places do:
STR(col_1,LEN(col_1),1)
Example:
DECLARE @a varchar(10),
@b varchar(25)
SET @a = '123.00'
SET @b = '000123.000'
SELECT
CASE
WHEN RTRIM(LTRIM(STR(@a,LEN(@a)+2,1))) = RTRIM(LTRIM(STR(@b,LEN(@b)+2,1))) THEN 1
ELSE 0
END
Otherwise stick to the normal CONVERT/CAST, as this query returns true:
SELECT
CASE
WHEN CAST('1.0' AS FLOAT) = CAST('1' AS FLOAT) THEN 1
ELSE 0
END
You can cast the numeric values to a float, and then back to a varchar, which will allow you to compare you varchar cols
Observe:
CREATE TABLE #table_1 ( col_1 varchar(10), col_2 varchar(10) )
INSERT INTO #table_1
SELECT 'A', 'A' UNION ALL
SELECT 'A', 'A' UNION ALL
SELECT 'A', 'A' UNION ALL
SELECT 'A', 'B' UNION ALL
SELECT '1.0', '1' UNION ALL
SELECT '1.2', '1' UNION ALL
SELECT '2.0', '2' UNION ALL
SELECT '2.2', '2.1'UNION ALL
SELECT '2.2', '2.0'
SELECT
col_1,
col_2,
CASE WHEN
CASE WHEN ISNUMERIC(col_1) = 1
THEN CAST( CAST( col_1 AS FLOAT ) AS VARCHAR(10) )
ELSE col_1
END
=
CASE WHEN ISNUMERIC(col_2) = 1
THEN CAST( CAST( col_2 AS FLOAT ) AS VARCHAR(10) )
ELSE col_2
END
THEN 'Equal'
ELSE 'Different'
END AS [comparison]
FROM #table_1
DROP TABLE #table_1
.0is not required when the value is1.