How to Convert from Varchar to Decimal

Question

How to Convert from Varchar to Decimal

Justin Doh

Mr or Mrs. 500

Points: 543
More actions
October 5, 2005 at 11:59 am

#67571

I am asking to see what I am doing wrong.
I am trying to Convert or Cast from Varchar to Decimal while I am doing DTS, and am constantly getting this error:
" Error converting data type varchar to numeric."
Currently, I have this data mode:
1) FROM Staging table:
varchar / length 3
2) TO Production table:
decimal / length 5 / Precision 3, Scale 0
I used this syntex to covert such as:
INSERT INTO tableB
FieldNameB
SELECT
CAST( FieldNameA AS decimal(3,0))
FROM tableA
I know SQL's Book on line mentions as Implicit conversion, and so this is the reason that it should automatically convert, but I don't have any clue
what to do at this moment.
Appreciate for any feedback.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

At the moment, I would doubt that all the data coming in adheres to your specified format. Run something like the following:

SELECT * FROM tableA WHERE ISNULL (FieldNameA, '') NOT LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

My guess is that you'll come up with at least one value that is not in the expected format.

Justin Doh Mr or Mrs. 500 Points: 543 More actions · Answer 2

Hi Lee,

I really appreciate for your reply.

I tried that query, but no data is found.

Should I try something like numeric or integer instead of decimal?

Justin

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

Can you show some of the data?

I think I could have given you a better query earlier. Try:

SELECT * FROM tableA WHERE ISNULL (FieldNameA, '') = '' OR ISNULL (FieldNameA, '') NOT LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

Justin Doh Mr or Mrs. 500 Points: 543 More actions · Answer 4

Hi Lee,

Thank you for your help.

Yes, I found 4 null or empty data in that field.

If that is the result, would that mean I cannot convert empty varchar data into

decimal or numeric or integer by itself?

Justin

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 5

Off the top of my head, I'm thinking NULL should have worked if the accepting table column allows NULL, and in any event if it didn't, that's a whole nother error message. It seems more likely to me that you have spaces in your source.

Either way, the following ought to take care of it:

If the receiving column allows NULL:

INSERT INTO tableB (FieldNameB)
SELECT CASE ISNULL (FieldNameA, '')
           WHEN ''
           THEN NULL
           ELSE CAST (FieldNameA AS DECIMAL (3,0))
         END
FROM tableA

If the receiving column does not allow NULL:

INSERT INTO tableB (FieldNameB)
SELECT CAST (FieldNameA AS DECIMAL (3,0))
FROM tableA
WHERE FieldNameA IS NOT NULL
AND FieldNameA != ''
AND FieldNameA LIKE REPLICATE ('[0-9]', LEN (FieldNameA))

T-SQL has an ISNUMERIC function, but for my tastes it is far too tolerant of non-numerics. T-SQL needs an ISREALLYTRULYNUMERICHONESTTOGOD function.

Justin Doh Mr or Mrs. 500 Points: 543 More actions · Answer 6

Hi Lee,

It worked!!!

Thank you so much for your help.

Justin

UKGav Say Hey Kid Points: 678 More actions · Answer 7

Lee Dise (10/5/2005)
T-SQL has an ISNUMERIC function, but for my tastes it is far too tolerant of non-numerics. T-SQL needs an IS-REALLY-TRULY-NUMERIC-HONEST-TO-GOD function.

I made a function for this and named it "isReallyNumeric" ! 😀

Here you go:

/********************************************************************

Function Purpose:

This function will return a 1 if the string parameter contains ONLY

numeric (0-9) digits and will return a 0 in all other cases.

~ Created by: Gavin Broughton

~ Twitter: http://www.twitter.com/ukgav

~ Website: http://www.varchar.co.uk

********************************************************************/

CREATE FUNCTION [dbo].[udf_isReallyNumeric](@inputstring VARCHAR(4000))

RETURNS int

BEGIN

DECLARE @output int

SELECT @output =

(CASE WHEN NULLIF(@inputstring,'') /* If string is empty */

NOT LIKE '%[^0-9]%'/* and LIKE numbers 0-9 (NOT LIKE double negative needed here) */

THEN 1 ELSE 0 END)/* then return int 1 */

RETURN @output

END

Enjoy! 🙂

- UKGav

- Twitter: http://www.twitter.com/ukgav