SQLTeam.com | Weblogs | Forums

Converting datatype varchar to numeric and date


#1

I have a temp table that I imported using SSIS. I was having some trouble importing the data as anything other than varchar. The end result is to take the data in this table and copy it to 3 other tables but I need to copy some of the varchar columns as numeric and some as datetime.

I also have 4 columns called century, year, month and day that need to be concatenated and copied to another table as a datetime.

Thanks in advance.

Scott


#2

Not enough information. What trouble are you having?

Also, please post:

  1. sample of your input data
  2. CREATE TABLE statements for the destination tables.

#3

In general, you would use CAST or CONVERT to convert the data from VARCHAR to numeric. Post some examples of your input data and the output you need to get more details.


#4

The varchar that needs to be converted to string are numbers ranging anywhere from 1-12 digits. Here is a sampling of a few of the columns

183	67	Delete	5912	Drug Stores (Pharmacy)	100146458	2	5/7/1982 0:00
191	67	Delete	5993	Cigar, Cigarette, Tobacco Stor	0	1	11/17/1981 0:00
205	67	Deleted	5814	Fast food	0	1	11/10/1996 0:00
213	52	Term Outstanding Bal	5921	Liquor Store	0	0	1/1/1984 0:00
221	52	Term Outstanding Bal	0	Unknown or blank code	0	0	4/26/1982 0:00
230	57	C/O Comp Agent Dele	5814	Fast food	0	0	5/7/1982 0:00

The tables I am copying into are already created.


#5

Did you mean converted to NUMERIC? If you really meant "converted to string", there is nothing further you need to do. VARCHAR is already string.

If you do want to convert 12 digits to NUMERIC, what is the data type of the destination column(s), specifically those that can be 10,11 and 12 digits long? The maximum an int can store is some 10-digit number that starts with a 2.

One thing that possibly might work is to simply select from your staging table and insert into your destination table(s) without you doing any conversions. The varchar columns should implicitly be converted to numeric. If that does not work - i.e., if you are getting some errors, that means there are some strings (varchar values) that cannot be converted to the numeric data appropriate for that column. There are some limitations to implicit conversions - the table on this page shows what can be converted to what.


#6

We add two columns to that type of Staging Table:

ErrorNo
ErrorMsg

and we run some SQL on the Staging Table to check if the data in conforms, such as "Should be a valid date" or "Should be numeric". Any rows that don't pass the test get an error number (first error found only) and a concatenated list of error messages. We then don't import any rows where ErrorNo <> 0 and we have a report that the user can run to see the ErrorMsg (and data) on rows that have an error - so they can fix up the data in the source system before the next import.


#7

Yes - I meant Numeric.

The columns in the table I need to copy to are also numeric.

I will try copying anyway and see what happens.

I may not get to this before I leave today and then have a long weekend so I will update you next week.

Thanks for the suggestion.


#8

Don't rely on IsNumeric() to test a string as being a valid numeric value. There are invalid strings that it gives a false-positive result for :frowning:

A reg-ex expressions is usually good enough e.g.

SELECT	CASE WHEN TestValue LIKE '%[^-0-9.]%' THEN 1 -- Non numeric character
             WHEN TestValue LIKE '_%-%' THEN 2 -- minus sign not at the front
             WHEN TestValue LIKE '%.%.%' THEN 3 -- two or more decimal places
             ELSE 0
             END AS [Test],
	IsNumeric(TestValue) AS [IsNumeric],
	'"' + TestValue + '"'
FROM
(
    SELECT '12345' AS TestValue
    UNION ALL SELECT 'A12345'
    UNION ALL SELECT 'A2345A'
    UNION ALL SELECT '123A45'
    UNION ALL SELECT '-12345'
    UNION ALL SELECT '12345-'
    UNION ALL SELECT '123-45'
    UNION ALL SELECT '.12345'
    UNION ALL SELECT '12345.'
    UNION ALL SELECT '123.45'
    UNION ALL SELECT '.123.45'
    UNION ALL SELECT '123.45.'
    UNION ALL SELECT '1E1'	-- Scientific format, very unlikely that would be valid in your data!
    UNION ALL SELECT '1.1'
    UNION ALL SELECT '1,1'
    UNION ALL SELECT '1,000'
    UNION ALL SELECT ' 1'	-- This might reasonably be included as "allowed"
    UNION ALL SELECT '$1'	-- might reasonable be included, but won't convert to NUMERIC
) AS T
--
ORDER BY [Test], [IsNumeric], TestValue