From: Mick Shea To: All

Does anyone know how to do a case insensitive search with SQL? We need to find company names "TIME..." and "Time..." in the same search (CMPNM LIKE "time%"). Any help would be greatly appreciated.

From: James Coolbaugh To: Mick Shea

It would be really nice to be able to do this; however, SQL does not provide any translate keywords. Therefore, SQL itself is limited to case-sensitive searches and comparisons. If you wanted to write a complicated select, you could probably do something like this:

 SELECT * FROM FILE WHERE SUBSTR(NAME,1,1) IN ('T','t') AND SUBSTR(NAME,2,1) IN ('I','i') AND SUBSTR(NAME,3,1) IN ('M','m') AND SUBSTR(NAME,4,1) IN ('E','e') 

This would work, but I don't think it is a good solution to the problem. Maybe IBM will provide a built-in function someday to do translation.

From: Mick Shea To: James Coolbaugh

Thanks for your help. The SUBSTR IN definitely is an option we hadn't explored. I was hoping there was a mapping function we had overlooked that would map lowercase into uppercase for the search. I guess not. (We are using translate tables on the logicals for positioning case insensitive.)

From: James Coolbaugh To: Mick Shea

I wish that IBM would provide a built-in function for translation. I really think IBM needs to provide this capability. It is available in almost every other place on the AS/400, so why not here?

From: Mick Shea To: James Coolbaugh

We received V2R2 yesterday. The change bars in SQL mark a new function: TRANSLATE. It works great except for one quirk: the TRANSLATE has to be on the left of the LIKE statement. TRANSLATE(company) LIKE 'TIME%' works; 'TIME%' LIKE TRANSLATE(company) yields a syntax error. So we've learned that we only need to ask the question the right way.