Convert Char to Numeric

[anrver]

Hi,
I'm busy placing a lot of TXT files into tables. I'm working with temporary tables who are an exact copy of TXT files. All columns of the temporary table are 'char' formatted. On inserting the temporary records into a definitive table I want to use the CAST function.
Im looking for a way to convert any character formatted value into numeric. For the moment I use the following code :

INSERT
INTO def_table
(InvNo,Amount)
SELECT InvNo,
CASE WHEN isNumeric(Amount)= 1 THEN cast(Amount AS numeric(18, 2)) ELSE cast(0 AS numeric(18,2)) END

FROM temp_table

This works but not in all cases. For example :
'999,9' is ok
'6700,77' is not : isNumeric ('6700,77') = 0 (For my standards this IS numeric)

Is there general code or function that covers all possible formats like for example:
' 8'
'1 '
''
'6000,99'
'1.500,88'
'000000008'

I've spend hours and hours(=days) to figure this out. So if someone can help me on this I would be very thankfull.

Many thanks






Erik Verstichel
Panalpina World Transport.
Antwerp, Belgium

 

[nigelrivett]

afraid not.
isnumeric will also think 123e12 is numeric (or something like that).

you could get rid of all ".", replace all "," with "." run isnumeric on the result and then check for other characters

something like
if isnumeric(replace(replace(@s,'.',''),',','.')) and patindex('%[^0-9 .,]%',@s)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.