converting nvarchar to integar

• simon.lewis-796464

  SSC Enthusiast

  Points: 134

  More actions

  December 22, 2008 at 11:24 am

  #197772

  Hi

  I need to sort on a column that has a variable character field of nvarchar. When I sort on this column i get the order as below:-

  10001

  11

  12

  13001

  14

  150

  160

  but I want :-

  11

  12

  14

  150

  160

  10001

  13001

  So, I need to convert the nvarchar into an integer. Can anyone help?

• ps.

  One Orange Chip

  Points: 29422

  More actions

  December 22, 2008 at 11:35 am

  #916292

  create table #temp1

  (col1 nvarchar(10))

  insert into #temp1

  select 1

  union all

  select 100

  union all

  select 1001

  union all

  select 20

  union all

  select 500

  select * from #temp1 order by cast(col1 as int)

  ** this wud run only if col1 has all numeric data

  ** to exclude non-numeric values, you may hv to change to query...

  select * from #temp1 where isnumeric(col1)=1 order by cast(col1 as int)

  ---

  
  Pradeep Singh

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 22, 2008 at 11:47 am

  #916296

  i use something like this, involving an ORDER BY featuring a case statement:

  i want nulls at the end, but non-numerics at the beginning;

  ORDER BY

  CASE WHEN ISNULL(Yourfield) = 1 THEN 999999

  WHEN ISNUMERIC(Yourfield) = 1 THEN CONVERT(int,YOURFIELD)

  WHEN ISNUMERIC(Yourfield) = 0 THEN 0

  END

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  October 16, 2009 at 5:25 pm

  #1067265

  create table Dept (

  description varchar(50) NULL

  )

  this works:

  ----------

  select cast(description as money)

  from Dept

  this fails:

  --------

  select cast(description as decimal(4,2))

  from Dept

  Q:

  in select statement, how do you convert from varchar(50) to decimal(4,2)