Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Wed Aug 01, 2007 4:37 am Post subject: Student Problem
I assume that this is a student problem. I solved it with CASE, will only work for the given type of input. Will only work for Num-Space (numbers 0-9), and Alpha-num-num. Will not work for numbers > 9 or Alpha-num-alpha. Nor an entry greater than 3 chars.
change all 'TEXT' to <your column name>
change '<table>' to <your table name>
Code:
SELECT TEXT
, CASE
WHEN SUBSTR(TEXT,1,1) BETWEEN '0' AND '9' THEN CONCAT('0',SUBSTR(TEXT,1,1))
ELSE CONCAT('1',SUBSTR(TEXT,1,1))
END AS SORT_KEY1
, CASE
WHEN SUBSTR(TEXT,3,1) BETWEEN '0' AND '9' THEN CONCAT('0',SUBSTR(TEXT,3,1))
WHEN SUBSTR(TEXT,3,1) BETWEEN 'A' AND 'Z' THEN CONCAT('1',SUBSTR(TEXT,3,1))
ELSE ' '
END AS SORT_KEY2
, CASE
WHEN SUBSTR(TEXT,2,1) BETWEEN '0' AND '9' THEN CONCAT('0',SUBSTR(TEXT,2,1))
WHEN SUBSTR(TEXT,2,1) BETWEEN 'A' AND 'Z' THEN CONCAT('1',SUBSTR(TEXT,2,1))
ELSE ' '
END AS SORT_KEY3
FROM <table>
ORDER BY SORT_KEY1
, SORT_KEY2
, SORT_KEY3;
_________________ Dick Brenholtz
American living in Varel, Germany
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
