Hi all,
This isn’t an Access question, however it is a question regarding a database, so I hope somebody can help me out!
I’ve got an Oracle database (9i), that has a varchar2 column (ID) on a table (entity).
The data I’m filtering all have values in this column beginning with TRS- (for example, TRS-1, TRS-29, TRS-1883).
I’ve been able to create sql statement to sort the values numerically, by ignoring the TRS- characters:
select id from entity where id like 'TRS-%' order by to_number(substr(trim(id),5));
The outcome would sort the above values as:
TRS-1
TRS-29
TRS-1883
rather than the alpha order of 1, 1883, 29.
This works fine and dandy, until there’s an input error on the ID column.
What code can I use to create another view for “trouble shooting”? I’m looking to create a view that will basically show only the problem values (for example, TRS-a312, TRS- 221, TRS-?213).
There is no input validation on the ID column. It’s simply user entry. Staff have been in the habit of creating a smart number system (thus the TRS-) for entities of like attributes. In this case, they are all traffic road signs. Also of note, the ID column is 20 characters in length. These entities are in order, from 1, to a current value of 5730.
Thanks in advance for any help you can offer.
