Converting Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Field

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2005-07-29 : 11:57:13

I have a table that has a units column that is varchar because some of the
units have text in them. I'm attempting to update the units into a new
field that is called Fxd_Units.

Update Tbl_MyTable
Set Fxd_Units = Cast(TxnUnits as Numeric)
Where TxnUnits not like '%M%'

This throws the error converting error.

Any help is appreciated.

Cheers,

Job

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-29 : 12:00:15

you probably have some other letter in your column.
try
Where TxnUnits not like '%[a-zA-Z]%'

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 12:08:45

Would


Update Tbl_MyTable
Set Fxd_Units = Cast(TxnUnits as Numeric)
Where IsNumeric(TxnUnits)=1

help?

Spirit: I think I would do

Where TxnUnits not like '%[^0-9.]%'

to isolate to numeric characters (if I've got my double negatives right!)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-29 : 12:12:49

watch out for this:
select isnumeric('123D4'), isnumeric('123E4')

Go with the flow & have fun! Else fight the flow

Job
Yak Posting Veteran

69 Posts

Posted - 2005-07-29 : 12:13:37

Hmm..that didn't work..this is a medical units field, most are simple like 1,8,3 etc. However, with Anesthesia units you get something like 80M,2.3,5,7,,,,,. I did check and there aren't any other letters. You do have the commas, but there are not other characters except where there is an 'M' in the field..

Job
Yak Posting Veteran

69 Posts

Posted - 2005-07-29 : 12:20:07

Ok it looks like the Where IsNumeric(TxnUnits)=1 worked!

Cheers!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-29 : 12:23:56

EDIT:
glad you worked it out...

well... you could put the column in question into a temp table with an identity
and then try a bisection method to find which row returns the error...
like:


select identity(int, 1, 1) as id, TxnUnits
into #temp
from Tbl_MyTable

select Cast(TxnUnits as Numeric) from #temp
where id between 1 and 1000  -- and then change these 2 accordingly when you get the error...
      and TxnUnits not like '%M%'

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 12:30:24

I have an alternative plan I try when this sort of thing happens (just in case it is of passing interest):


SET ROWCOUNT 100
Update U
Set Fxd_Units = Cast(TxnUnits as Numeric)
-- SELECT TOP 1 *
FROM Tbl_MyTable U
Where TxnUnits not like '%M%'
      AND Fxd_Units IS NULL
SET ROWCOUNT 0

I set the ROWCOUNT to some reasonably large number, then run it repeadly until I get an error. Then I reduce the rowcount by half each time I get an error. Once its down to 1 I use the SELECT part to see what the duff row was.

SQL Server seems to keep things in order, so that the SELECT shows the duff row, rather than some other random row - but that may have been serendipity up to now.

Kristen

Job
Yak Posting Veteran

69 Posts

Posted - 2005-07-29 : 12:44:11

Very good. Thanks for the education. I'll use both of these methods to troubleshoot in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources