I have an odd type of sorting that is needed. I have a field in the form of :
Letter Letter Number Letter Letter Letter but the field could have
Letter Number Letter Letter Letter OR
Letter Number Letter Letter
Basically I want to sort alphabetically by the characters to the Right of the number.
Not sure how to do this.
You need a new field generated from the field you have based upon your criteria. You can do this in your SQL statement which you probably have for input to your report. There are better methods to do this in other DB’s, but this is the best I could come up with using HSQLDB SQL. It utilizes the CASE statement but needs to be nested 10 x’s to cover each of the ten digits. Here is the statement I used as a test:
select ID, STRFIELD,
CASE WHEN LOCATE('0',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('0',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('1',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('1',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('2',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('2',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('3',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('3',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('4',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('4',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('5',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('5',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('6',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('6',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('7',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('7',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('8',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('8',STRFIELD) + 1) ELSE
CASE WHEN LOCATE('9',STRFIELD) > '0' then SUBSTR(STRFIELD, LOCATE('9',STRFIELD) + 1) ELSE
STRFIELD END END END END END END END END END END
AS "SORTFLD" from TESTSAMPLE
This will find any single digit and return the portion after that digit. Here is the result:
STRFIELD was input to the process and SORTFLD was the result. Note: If the input does not contain a numeric digit, the entire string is returned.
If this answers your question please click on the 
(upper left area of answer).
thx… I will give this method a try.
this works running as an SQL command. How would I put this ‘filter’ into my table that has all the data?
If I understand your comment correctly, you cannot put the SQL into your table. You can create a new field and update that new field with an “UPDATE” SQL statement and thus create the new information, but then you would need to do that for each new record entered into the table.
To do this automatically for each new record, specific macro code would need to be written to read the input data, create the new data based on that and then move it to the new field for writing to the record.
I’m going to amend my last comment. Using a newer HSQL (v 2.x as in a split DB) or other external DB’s, it may be possible to do this automatically for new records using a DB trigger. I will try testing & get back to you on this. The already entered records would still need creation via SQL update statement.
@rotormansa I have successfully tested creating a trigger based upon the above discussions. If you are interested, please create a new question such as “Generate trigger in split DB to create field derived from another”. I ask for a new question (again if interested) not only because it requires quite a bit of explanation, but also this subject is rarely asked for in this forum and could be of benefit to others. If the answer were buried here it may not be found.
just posted… many thanks!