SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Convert from Varchar to Decimal


How to Convert from Varchar to Decimal

Author
Message
Justin Doh
Justin Doh
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 1

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.


Lee Dise
Lee Dise
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 21

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
Justin Doh
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 1

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
Lee Dise
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 21

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
Justin Doh
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 1

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
Lee Dise
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 21

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
Justin Doh
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 1

Hi Lee,

It worked!!!

Thank you so much for your help.

Justin


UKGav
UKGav
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 257
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" ! :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search