Hi
Newbie to SQL 2000, and i'm having a bit of a problem

I have a varchar column containing data such as '99 702 556'
However when i use the following select to retrieve and sort it, it's sorted alphabetically, instead of numerical

-- Star
select homedirsiz
from dbo.tblhistor
where homedirsize is not nul
order by homedirsize des
--Finis

I've tried different things, both convert and cast without getting i
right:

-- Star
select convert(int,homedirsize
from dbo.tblhistor
where homedirsize is not nul
order by homedirsize des
--Finis

-- Resul
Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '99 702 556' to a column of data type int
-- Resul

Anyone have any idea on how i can convert it to numerical to get it sorted by number size, or how i can remove the whitespaces and then convert it? I assume the problems with using convert/cast is due to the white spaces..

Regard
Bjørn Tor

Tanx for the quick reply
However, this does not seem to work for me.
I've tried
select convert(int,REPLACE(homedirsize, ' ','')
from dbo.tblhistor
where homedirsize is not nul
order by homedirsize des

and the result is
Server: Msg 245, Level 16, State 1, Line
Syntax error converting the varchar value '99 702 556' to a column of data type int

Are there any checks i can do to find out the problem

Got some help from a guy at work:

The problem was that the space was not a space ... so this works..

select convert(bigint, replace(homedirsize, char(160), '')
from dbo.tblhistor
where homedirsize is not nul
order by convert(bigint, replace(homedirsize, char(160), '')) des

Hi Bjorn,

You can remove the spaces by replacing them with an empty string:

select convert(int,REPLACE(homedirsize, ' ',''))
from dbo.tblhistory
where homedirsize is not null
order by homedirsize desc

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]
by number size, or how i can remove the whitespaces and then convert it? I
assume the problems with using convert/cast is due to the white spaces.. ?
[quoted text, click to view]