fix data and Convert nvarchar to int

[NightZEN]

I have a SQL server DB table that has data in nvarchar fields like this:

InspecReportNumber Date Lot Qty DispDeQty DispSoQty DispRewQty DispScQty DispRetQty DispUseQty

6007 8/1/2002 100%
6008 8/1/2002 10
6009 8/2/2002 50
6010 8/2/2002 as needed 5
6011 8/2/2002 216 216
6012 8/2/2002 105 1 104
6013 8/2/2002 100% 100
6014 8/2/2002 21 21
6015 8/5/2002 85 all 25
6016 8/5/2002 44
6017 8/5/2002 300
6018 8/5/2002 103 103 1
6019 8/5/2002 10
6020 8/6/2002 6 6 1 5

I want to convert the disp* fields from nvarchar to int data types, but before I do, I want to replace all no-numeric data with the number in the Lot_Qty field. Does anyone have a method for doing this?

Thanks,
Keith

 

[vongrunt]

Try something like:

update mytable
set mycolumn = Lot_Qty
where isnumeric( mycolumn ) = 0

Isnumeric() isn't perfect but will get rid of 95% of mess... the rest should be easy.

 

[SQLSister]

OK first it strikes me that LOtQTY also has nonnumeric data.

Select LotQuantity from table1 where
Isnumeric(LotQuantity) = 0

This will tell you what nonnumerics you have. You can then write one or more updates to fix them. You might want to replace all with null or 0 and 100% with 100 or whatever.

Then again once you have all numerics in LotQuantity you write an update statement to fix the other columns again using the IsNumeric function. Something along the lines of:

Update table1
Set col2 = col
where isnumeric(col2) = 0

Questions about posting. See faq183-874

 

[NightZEN]

This works great, except some rows will have a null, and I don't want to change those rows. Will something like this work?

Update table1
Set col2 = col
where isnumeric(col2) = 0 and isnull(col2) = 1

thanks

 

[NightZEN]

I figured it out. Thanks for your help SQLSister and vongrunt!

Update table1
Set col2 = col
where isnumeric(col2) = 0 AND col2 IS NOT NULL