ansaurus

Question

Ordering by the first alphabetical char in a column in MYSQL

Answer 1

+2 A:

Write an expression that will return the first alphabetical character, then just Order By [that expression]

  Order By substring(LTrim(
           Replace(Replace(Replace(Replace(Replace(
           Replace(Replace(Replace(Replace(Replace(
             colname, '1', ''),'2',''),'3',''),'4,''),'5', ''),
                      '6',''),'7',''),'8',''),'9',''),'0',''))
                   1,1)

If you want the rows sorted by the entire city name, and not just by the first character (as question title specifies) then use this:

  Order By LTrim(
           Replace(Replace(Replace(Replace(Replace(
           Replace(Replace(Replace(Replace(Replace(
             colname, '1', ''),'2',''),'3',''),'4,''),'5', ''),
                      '6',''),'7',''),'8',''),'9',''),'0',''))

Above is a guess (I haven't tried it), but the idea is first delete all numeric characters from the column value, then take the first character of whatever is remaining.

Also, if this works, and if you have any development access to the dataabse, (thinking DRY principle), I would add a computed column to this table, (or a separate view against the table), that is defined to use the above expression, so that this "extraction" of the town name is available to all other code that might want to access it without copying this expression everywhere you may need it..

Charles Bretana 2010-01-20 15:33:50

I'm sure you just solved his problem.

zneak 2010-01-20 15:35:16

I think this will be the best way. I'm not familiar with how to write expressions using PHP. Can you advise on the best way to do this?

baritoneuk 2010-01-20 15:40:25

Why take the first char only? What if 2 names start with the first char?

Veger 2010-01-20 15:41:16

OK, I'll try this. Just concerned that this will slow the query down quite a bit. I agree with Veger that it shouldn't just be the first char, probably the first 3 or 4 to be on the safe side. Also, I should probably add a hyphen since some addresses have that in before the postal town.

baritoneuk 2010-01-20 15:47:16

if you're easking what would I do if it were my database, and my problem, then yes I would probably do it that way. But, assuming the OP knows what he asked, I answered the question he asked, not one I think he should have asked...

Charles Bretana 2010-01-20 15:48:25

@Baritoneuk, If that is so, then why is your question enttiied "Order By the FIRST alphabetical character .... " ??

Charles Bretana 2010-01-20 15:49:37

@Baritoneuk, if you use this, yes add another replace for the hyphen, and for any other character that might appear before the first alpha you want the sort to start with ...

Charles Bretana 2010-01-20 16:00:18

Answer 2

+1 A:

First thing that comes to mind to me would to do the following on my ORDER BY, obviously, adding numbers 0 through 9. You'll notice crappy schemas produce crappy solutions. :) As the gentleman said above, you should probably think about a redesign of how you are storing your town data.

ORDER BY REPLACE(REPLACE(REPLACE(FieldName, '1', ''),'2',''),'3','')  ETC.

George 2010-01-20 15:35:29

Could you use a regex replace instead of nested REPLACE's?

FrustratedWithFormsDesigner 2010-01-20 15:37:27

I like the thinking, but this might be a little labour intensive. Isn't there a way of removing numeric chars in MYSQL like there is in PHP?

baritoneuk 2010-01-20 15:39:40

Two different balls of wax.

George 2010-01-20 15:40:55

OK, I know it might be a bit of a crappy schema. But what is the alternative? If there is a number before the town in the postal town in certain countries, what can I do about that?

baritoneuk 2010-01-20 15:59:41

I have looked at international address formats a bit more- http://www.bitboost.com/ref/international-address-formats.htmltechnically the number before the postal town is the postal code, but in some countries it should appear before the postal town and not after (as in the UK). It would be better to put the postal code in the postal code column and then by detecting the country place the postal code before the postal town if need be. Sounds like a lot of extra work!

baritoneuk 2010-01-20 16:08:30

Answer 3

+2 A:

You could write a stored function which returns the remainder of the column starting at the first alphabetic character (perhaps using REGEXP to find that index). Then order by the stored function.

Edit: instead of regexp in your function, depending on data format you could do a 'substring_index' on ' ' (space) and return the index of the first space, then call substring to return the remainder of the string after the first space.

Once you've created a stored function to return the string following the numbers, you can utilize it like this:

order by yourfunctionname(postalTown)

Stored Functions

Chris Kannon 2010-01-20 15:40:19

This sounds interesting, but I am not exactly sure what you mean and how to implement this one.

baritoneuk 2010-01-20 15:52:49

Baritoneuk, I've edited to give a tutorial to a stored function.

Chris Kannon 2010-01-20 16:16:46

Answer 4

+1 A:

Create a view on the table, making whatever translations you need, and then query against the view?

Unsliced 2010-01-20 15:53:22

ansaurus

tags:

views:

answers:

Ordering by the first alphabetical char in a column in MYSQL

related questions