i have a Nvarchar Column in a table in SQl Server 2005?

Question

column is a nvarchar (15)

that above column consists of digits and characters for example

column consists data like row 1: 60 Units row2: 2 Units row3: 100 Units row 4 :1000 Units

i want to only numeric values into separate column? how can i do that i tried but i don't get it i show you my query

SELECT CONVERT(INT, column) as abc from table

Answer 1

If your data actually contains units, then you could use replace:

select 
  cast(replace(col, ' units', '') as int) col
from yt

See SQL Fiddle with Demo

You could also use substring and charindex to return everything before the word:

select cast(substring(col, 1, charindex(' ', col)) as int) col
from yt

See SQL Fiddle with Demo.

If you might have number values without the units, then you can use:

select 
  case 
    when charindex(' ', col) > 0
    then cast(substring(col, 1, charindex(' ', col)) as int)
    else cast(col as int) end col
from yt;

See SQL Fiddle with Demo