I am attempting to convert a varchar to numeric. However, I am trying to convert just a substring of the varchar.
I know with 100% certainty that this query only returns numeric info. However, it won't let me convert the substring. (I tried cast, too.) I've consulted books online. I am just not sure whether my using substring is possible.
SELECT SUBSTRING(Col4,1,CHARINDEX('%',Col4)-1)
FROM Executive.StagedimWeight
WHERE Col4 IS NOT NULL
AND Col4 LIKE '%\%%'ESCAPE '\'
The above query returns:
35
65
However, if I try this convert syntax:
SELECT CONVERT(NUMERIC(5,4),SUBSTRING(Col4,1,CHARINDEX('%',Col4)-1))
FROM Executive.StagedimWeight
WHERE Col4 IS NOT NULL
AND Col4 LIKE '%\%%'ESCAPE '\'
I get: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
Any help greatly appreciated!