Change field with numeric and alphanumeric to numeric

I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

RandyDoes this help

set nocount on
go
create table #test (col1 char(10), col2 char(10))
go
insert #test values ('BU89090', 'record 1' )
insert #test values ('GHJ9213', 'record 2' )
insert #test values ('79898', 'record 3' )
insert #test values ('89067', 'record 4' )
insert #test values ('09889067', 'record 5' )
go

select *
from #test
go
select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
from #test
go
drop table #test

Output
col1 col2
---- ----
BU89090 record 1
GHJ9213 record 2
79898 record 3
89067 record 4
09889067 record 5

intCol1 col2
---- ----
NULL record 1
NULL record 2
79898 record 3
89067 record 4
9889067 record 5|||achorozy,

Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

This is sure a different animal than MS Access!

Thanks again,

Randy