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!