Complex methods

[jda]

Hi Ruslan,

I think I know the answer to this, but I thought I'd ask...

I want to string together two string operations in a method. The 
first strips out certain words from the string. The second returns 
the first line (ending in a return charcater) in that string.

I have done this, and it works, but the SQL code for each section of 
a CASE/WHEN/ELSE clause is enormous (here's an example from a single 
WHEN clause, for just one word to be removed from the string):

IF(Left((CASE WHEN 
LOCATE(UPPER('al-'),UPPER(coalesce(authors,editors)))=1 THEN 
RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - 
LENGTH('al-')) WHEN LOCATE(UPPER('von 
'),UPPER(coalesce(authors,editors)))=1 THEN 
RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - 
LENGTH('von ')) WHEN LOCATE(UPPER('the 
'),UPPER(coalesce(authors,editors)))=1 THEN 
RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - 
LENGTH('the ')) ELSE coalesce(authors,editors) END)), 
(LOCATE(chr(13), (CASE WHEN 
LOCATE(UPPER('al-'),UPPER(coalesce(authors,editors)))=1 THEN 
RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - 
LENGTH('al-'))

This makes debugging terribly difficult, and of course can easily 
generate SQL statements of thousands of characters.

The question is, is there a simple way to get the result of the first 
SQL query and then feed that to the second, all within one SQL 
statement? I suppose I can create temporary fields to handle 
sequential calls, but I'd rather not (that gets complex, too).

I hope this question is clear.

Thanks,

Jon