sql server - How can I select integer values from an NVARCHAR column in a SQL query? -

i'm using sql server 2005.

i'm trying values of fk_unitserial integers. column defined nvarchar(255), not null.

i think i'm selecting numeric values column , determining if values in specific range using following query:

select t.pkid, t.fk_unitserial (     select r.pkid, r.fk_unitserial     tbl_ovenresults r     isnumeric(r.fk_unitserial + '.0e0') = 1 ) t convert(decimal(38, 0), t.fk_unitserial) between -2147483648 , 2147483647 order t.pkid 

but running gives me message:

msg 8114, level 16, state 5, line 1 error converting data type nvarchar numeric. 

it looks offending value 129896545820, running

select convert(decimal(38, 0), 129896545820) 

works expected.

what missing?

edit

i guess i'm asking, maybe, if isnumeric ever returns 1 values can't used in convert(decimal(38, 0), value).

sql server reserves right rearrange expressions, exception of case statement. means can have offending values passed subquery outer query.

the 1 exception case statement, sequential evaluation followed (there asterisk when involves aggregated expression, not issue here). suggest:

select r.pkid, r.fk_unitserial tbl_ovenresults r (case when r.fk_unitserial not '[^0-9]' convert(decimal(38, 0), t.fk_unitserial) end)           between -2147483648 , 2147483647  order t.pkid; 

as note. expression isnumeric return true value '-'.