May 16, 2008 at 4:08 pm
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?
May 16, 2008 at 4:50 pm
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.
May 16, 2008 at 5:11 pm
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!
May 16, 2008 at 6:02 pm
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
May 19, 2008 at 7:32 am
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.
May 19, 2008 at 7:54 am
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"
May 19, 2008 at 6:40 pm
...or 52,000...
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply