Data Conversion question - bad data points

Question

Post reply

Data Conversion question - bad data points

wayne.mcdaniel

SSCommitted

Points: 1546
More actions
May 16, 2008 at 4:08 pm

#185616

I'm trying to convert some data from old text files imported from an AS400 system to SQL 2000
I used DTS to dump the information into SQL, now I'm trying to convert the raw data into something I can use. For any numbers involving money, they were stored as a zero padded string with no decimal point, so $10.75 is stored as '00001075'.
This is pretty easy, but there are a few odd data points where there is a character where I would expect a number. Is there some way to test a character string before I try to CAST or CONVERT, so I don't get an error message? Preferably something that would let me discard that data point and move on.
Here's a very simple sample of what I'm trying to do
Create Table #DemoTable
(DemoID Int,
Amount Char(10),
AmountFixed Numeric(10,2) NULL
)
Insert into #DemoTable (DemoID, Amount, AmountFixed)
Select 1,'1075', NULL UNION ALL
Select 2,'0523', NULL UNION ALL
Select 3,'0412', NULL
Declare @ID as Int
Declare @Amount as Char (10)
Declare @AmountFixed as Numeric(10,2)
Declare TestCursor Cursor FORWARD_ONLY FOR
Select DemoID, Amount from #DemoTable order by DemoID
Open TestCursor
Fetch next from TestCursor into @ID, @Amount
While @@FETCH_STATUS = 0
BEGIN
Set @AmountFixed = Cast(@Amount as Numeric(12,2))/100
Update #DemoTable set AmountFixed = @AmountFixed where DemoID = @ID
Fetch next from TestCursor into @ID, @Amount
END
Close TestCursor
Deallocate TestCursor
Drop Table #DemoTable
This works as expected. The problem is those pesky bad data points...change the one of the inserted lines to something like this...
Insert into #DemoTable (DemoID, Amount, AmountFixed)
Select 1,'1075', NULL UNION ALL
Select 2,'052K', NULL UNION ALL
Select 3,'0412', NULL
And it fails miserably. Any suggestions on how to catch this in straight SQL on SQL 2000?

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

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

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Try IsNumeric().

if isnumeric(@Amount) = 1

begin

Set @AmountFixed = Cast(@Amount as Numeric(12,2))/100

Update #DemoTable set AmountFixed = @AmountFixed where DemoID = @ID

end

else

print 'bad'

The cursor is only for the demo, right? Otherwise, any number of people will jump on that.

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 2

The cursor is for the demo, but its in the live conversion...I'm sure someone can give me the flaming kill the cursor lecture, and they're possibly right, but for a 1 time process I'll take the heat.

I'm so happy about isNumeric...that did exactly what I needed to do

Thank you!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

wayne.mcdaniel (5/16/2008)
The cursor is for the demo, but its in the live conversion...I'm sure someone can give me the flaming kill the cursor lecture, and they're possibly right, but for a 1 time process I'll take the heat.
I'm so happy about isNumeric...that did exactly what I needed to do
Thank you!

Ok... just a little heat... why do you practice doing things the wrong way even if it's for one time stuff? It's like trying to quit smoking... "you're a puff away from a pack a day". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 4

I'll take the heat because the example code is a gross oversimplification of what I am trying to do. I'm importing multiple (badly) linked tables and adding adjusting entries along the way. I'll concede that the elegant way of doing things is the better way to do it.

I'm just asking you to concede that getting the conversion done and keeping my job, and learning along the way, is the best approach for this one. Each one can get better.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Are you really sure

Select 2,'052K', NULL UNION ALL

is a bad data point?

It could actually mean a negative number, "-520".

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

...or 52,000...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)