Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Convert from Varchar to Decimal Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2005 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2005 3:56 PM
Points: 7, 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.

Post #226334
Posted Wednesday, October 5, 2005 12:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:07 PM
Points: 284, Visits: 14

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.

 

 




Post #226339
Posted Wednesday, October 5, 2005 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2005 3:56 PM
Points: 7, 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

 

 

 

 

Post #226365
Posted Wednesday, October 5, 2005 1:09 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:07 PM
Points: 284, Visits: 14

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))




Post #226368
Posted Wednesday, October 5, 2005 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2005 3:56 PM
Points: 7, 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

 

 

 

 

 

Post #226376
Posted Wednesday, October 5, 2005 1:38 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:07 PM
Points: 284, Visits: 14

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.

 

 




Post #226387
Posted Wednesday, October 5, 2005 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2005 3:56 PM
Points: 7, Visits: 1

Hi Lee,

It worked!!!

Thank you so much for your help.

Justin

 

 

Post #226418
Posted Friday, January 6, 2012 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 10:08 AM
Points: 34, Visits: 186
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
Post #1231607
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse