Page 1 of 1

Issues with ORDER BY SUBSTR

Posted: Fri Jun 04, 2010 10:11 pm
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!

Re: Issues with ORDER BY SUBSTR

Posted: Fri Jun 04, 2010 11:05 pm
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

Re: Issues with ORDER BY SUBSTR

Posted: Fri Jun 04, 2010 11:22 pm
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.