When searching and listing them I want to remove any preceeding 'The ', 'An
', or 'A ' that occurs in the title and I also want to be able to search it.
The case statement as follows seems to work fine:
WHEN title LIKE 'The %' THEN RIGHT( title, length( title ) -4 )
WHEN title LIKE 'A %' THEN RIGHT( title, length( title ) -2 )
WHEN title LIKE 'An %' THEN RIGHT( title, length( title ) -3 )
END AS modtitle
But if I try a WHERE clause using the columns AS title I get an error.
Can anyone help me figure out how to search the CASEed column?
Are you trying:
WHERE modtitle = '... something ... '
Cause the WHERE clause only works on columns. If you want to
do a match on the result of the CASE, you have to do your case
WHERE ( case ...yourstuff... end ) = 'something'
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL