BBj SQL engine: Case insensitive search with SQL LIKE

Question

I'm using the following query to search by TITLE in the CDSTORE database, but the search is not case insensitive. How to make the search case insensitive? I thought about using the LOWER verb but I guess it is not defined by the BBj SQL engine.

SELECT TITLE, MUSICTYPE FROM CDINVENTORY WHERE TITLE LIKE '%s%' ORDER BY MUSICTYPE

Answer 1

I believe BBj doesn't support the LOWER verb, but instead, you could use the integrated CVS Function to the exact same thing. The following should be what you are looking for:

SELECT TITLE, MUSICTYPE FROM CDINVENTORY WHERE cvs(TITLE,8) LIKE '%s%' ORDER BY MUSICTYPE