[PLUG] HSQLDB string operations

[John Jason Jordan]

On Sun, 24 Feb 2008 12:49:20 -0800
John Jason Jordan <johnxj at comcast.net> dijo:

> So now that I have the data in Base I am again trying to sort the field based on the rightmost three characters of the selection string. Note that I need to sort on the rightmost three characters of the *selection string*, not the rightmost three characters of the *field*. Base will let me sort on the field, but in design view (the GUI) there seems to be no way to specify an Expression to sort on. Sorting on the field gives me this SQL:
> 
> SELECT * FROM "MainBank" WHERE ( ( "Selections" LIKE '%LFc-___%' ) ) ORDER BY "Selections" ASC
> 
> I need to change that ORDER BY so it reflects the rightmost three characters of LIKE '%LFc___%'. I have tried several things but all I get is error messages. I think I need a WHERE nested inside somehow, something like:
> 
> SELECT * FROM "MainBank" WHERE ( ( "Selections" LIKE '%LFc-___%' ) ) ORDER BY "Selections" WHERE ( RIGHT,3 ( LIKE '%___%' ) )  ASC
> 
> But that doesn't work, nor do any of my other attempts. Either my logic is wrong or I have misplaced parentheses, quote marks, or something.

Never mind. I got it sorted finally:

SELECT * FROM "MainBank" 
WHERE ( ( "Selections" LIKE '%LFc-___%' ) ) 
ORDER BY SUBSTR( "Selections", LOCATE(  'LFc-', "Selections")  + 4, 3) ASC