Re: Sorting on varchar field

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 07/23/04

  • Next message: sara: "time" 
    Date: Fri, 23 Jul 2004 14:21:39 +0530

    hi don,

    create a function as shown in following example. and use this function in
    the query as shown below.

    ex:
    create function dbo.ret_num (@str as varchar(8000))
    returns varchar(8000)
    as
    begin
          while patindex('%[^0-9]%', @str) > 0
              set @str = replace(@str,
                    substring(@str,patindex('%[^0-9]%', @str),1),'')
          return @str
    end
    go

    --query

    select column_name
    from <table>
    order by cast(dbo.ret_num(column_name) as int) 

    -- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
  • Next message: sara: "time"

    Relevant Pages