Issues with ORDER BY SUBSTR

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nycdev
Forum Newbie
Posts: 1
Joined: Fri Jun 04, 2010 10:09 pm

Issues with ORDER BY SUBSTR

Post by nycdev »

I have strings in the following format:

"14,000 Employees (ITO/Atlanta)"
"144,321 Employees (CS/SanRamon)"

My goal is to order them first by the text within the parentheses, and second by the number at the front of the string. The following syntax works for the first part (ordering by the text within the parentheses), but not for the second part (ordering by the number). Is it because I am not forcing the string to an integer correctly with the '+0' ??

Code: Select all

ORDER BY SUBSTR(ls_employee, POSITION('(' IN ls_employee)),0+substr(ls_employee, 1, 5) $_REQUEST[odir]
Any help is greatly appreciated!
Last edited by nycdev on Sat Jun 05, 2010 8:37 am, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Issues with ORDER BY SUBSTR

Post by requinix »

Split that field out into the multiple fields it should be.

Code: Select all

num    | L   | R
-------+-----+--------
14000  | ITO | Atlanta
144321 | CS  | SanRamon
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Issues with ORDER BY SUBSTR

Post by califdon »

Exactly. Rule #1 of First Normal Form: all columns must be "atomic"--that is, single valued. Relational calculus, the basis for SQL, isn't designed to search for "pieces" of data within a column. Now, I can just hear you saying, "But I don't have any control over that, I get this data from somewhere else." The point is, you have to PUT it in this format. It may add a step to your processing, but that's the price you pay for someone else not understanding relational databases either.
Post Reply