I have varchar column with both numbers and letters, like 1 thru 10 and
5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100,
since it is a varchar field, it sorts like 1,10,11... instead of
1,2,3...
Is there any way to handle this without having to make a sort order
column?
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org) 2 1592
If your string *always* begins with a numeral, this will work:
ORDER BY to_number(text_ field, text(99999999)) , text_field
If it doesn't always begin with a numeral, you have to ensure that it does, so a textcat of zero
ensure it does...
ORDER BY to_number(textc at('0', text_field), text(99999999)) , text_field
That works provided your number is never negative, (a reasonable assumption I think).
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes te***@greatgulf homes.com
Fax: (416) 441-9085 -----Original Message----- From: pg************* ****@postgresql .org [mailto:pg****** ***********@pos tgresql.org]On Behalf Of Robert Fitzpatrick Sent: Tuesday, August 10, 2004 8:55 PM To: PostgreSQL Subject: [GENERAL] Sorting varchar w/single digits
I have varchar column with both numbers and letters, like 1 thru 10 and 5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100, since it is a varchar field, it sorts like 1,10,11... instead of 1,2,3...
Is there any way to handle this without having to make a sort order column?
-- Robert
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Tue, Aug 10, 2004 at 08:54:45PM -0400, Robert Fitzpatrick wrote: I have varchar column with both numbers and letters, like 1 thru 10 and 5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100, since it is a varchar field, it sorts like 1,10,11... instead of 1,2,3...
Is there any way to handle this without having to make a sort order column?
Try something like this:
ORDER BY SUBSTRING(unitn um FROM '[0-9]+')::INTEGER, unitnum
--
Michael Fuhr http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alex Ayzin |
last post by:
Hi,
I have a column in my WinGrid, that's being populated with numeric data, but
it's of String datatype(business rule requires to have these numbers as
varchars in DB). On top of that, I need to be able to sort on that
particular column. But because column contains character data(even though it
looks like digits), the sorting is all out of whack, e.g, 1, 11, 12,
13.....2, 21, 22, so on, you got the picture.I'm using the typed dataset as...
|
by: pmud |
last post by:
Hi,
I am using teh following code for sorting the data grid but it doesnt work.
I have set the auto generate columns to false. & set the sort expression for
each field as the anme of that field... This grid displayes results based on
users search..
public static int numberDiv;
private void Page_Load(object sender, System.EventArgs e)
{
|
by: mike |
last post by:
If I had a date in the format "01-Jan-05" it does not sort properly
with my sort routine:
function compareDate(a,b)
{
var date_a = new Date(a);
var date_b = new Date(b);
if (date_a < date_b)
{ return -1; }
else
|
by: Foodbank |
last post by:
Hi everyone. I'm having trouble with this radix sorting program. I've
gotten some of it coded except for the actual sorting :( The book I'm
teaching myself with (Data Structures Using C and C++) just doesn't
explain things good at all and the tutorials I've viewed don't really
explain least significant digit first sorting or show examples, which
would be most helpful. I've commented the section where I know that
the iteration of the...
|
by: Guoqi Zheng |
last post by:
Sir,
The default paging of datagrid is somehow use too much resource, so I am
using Stored procedure for the paging. You can find my Stored procedure at
the end of this message.
It works fine to do paging like this, however, I have found it difficult to
do paging and sorting at the same time.
For example, in my case, my boss asked me to do sorting on EmailAddress,
| |
by: Roy |
last post by:
Greetings,
I've been avoiding it for so long, but like an evil wraith it always
returns to haunt me. The bane of my existence, it is... bidirectional
sorting!!!
Checked out previous posts and none seem to be of assistance to me.
Here's the scoop. I have a web app which populates a sortable, pageable
datagrid using a stored proc. Everything works great, however, I've
|
by: tfsmag |
last post by:
Hello,
I have a function that returns a dynamically created gridview. This
works fine, however it does not seem to be able to maintain state when
adding sorting or paging to the gridview. Does anyone have any idea how
to get this to work? below is the code. Please bear in mind that the
function is actually located in a seperate class file from the page
that actually returns the grid.
---code for function that returns the grid, this is...
|
by: D. |
last post by:
Hi,
I'm planning the structure of a SqlServer 2005 database for a new
application.
The requirement is that primary keys must be "natural"; i.e. in the table
Customers the primary key will be a max. 10 characters string (but the
string may be filled i.e. with only 5 charachters).
Should I define these primary keys as char or varchar?
I'm interested in your opinion in particular about performace issue, because
there will be tables with...
|
by: Oonz |
last post by:
Hi Friends,
How can we insert records in sorted order
like consider a table
No Name Phone
1 test1 12345
1 test1 23455
2 test2 68638
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |