We have a flat file that is used to interface data with a third
party and have been asked to split the file into three smaller
versions. The file contains a header record, detail records and
a footer record with total amounts on it.
I can split the file easily enough and add in a footer record,
but I need to update the footer with the totals for the new,
smaller, file. The trouble is that negative values are held in
"zoned" format, so -1234 is stored as "123M".
Is it possible in SQL to convert a character "zoned" value to
numeric? I've tried putting a UDF together (see below), but it
keeps hitting the exception handler whenever I run it for
CREATE FUNCTION CVTZONED (INZONED VARCHAR(15))
Any advice on how I can achieve the result I want, or debug the
UDF to see where it's failing would be most gratefully welcome.