SQL Search Using Wild Cards

[K.Forsythe]

'JO%SEPH%' or 'JO_EPH%' for just a single character. The extra % at the end makes all the difference. I can't tell you exactly why, it just works. I believe it has to do with trailing blanks. Kevin

[J.Wells]

SQL Search Using Wild Cards

Kevin, Thanks for the quick reply, your tip did the trick! Joe

[Guest.Visitor]

SQL Search Using Wild Cards

We had the same problem. Apparently (from what I can deduce), when you have a wildcard in the middle of the field, DB2 SQL is expecting the entire field to end with the remaining characters. For example: Let's assume that the field is 10 characters.

[*]If the search is 'JO%'
[*]This will find anything that starts with JO[*]'JOSEPH '[*]'JOE '[*]'JOHN '[/list][*]If the search is 'JO%EPH'
[*]This will return anything that starts with JO and ends with EPH[*]'JOblahxEPH'[*]'JO SEPH'[*]'JO12345EPH'[*]However, since 'JOSEPH ' actually ends with 4 blanks, this doesn't match.[/list][*]If the search is 'JO%EPH '
[*]This will return the results you are looking for[*]'JOSEPH '[*]'JOHEPH '[*]'JO1EPH '[/list][/list]The reason that 'JO%EPH%' works is because now the database engine is actually going to match the blanks at the end (using the wildcard). The way we got around this was that we knew there should never be two spaces in a row and so we could search for 'JO%EPH %' and be fairly confident that we would only get the results we were looking for. The only time this would cause a problem is if you were expecting to get 'JO12345EPH' (or anything that didn't have two spaces at the end). I guess one work around would be to increase the field size to ensure that you could have trailing spaces. All of this is direct contridiction to IBM's SQL books, as well as (from what I know) SQL standards. I'm not sure if this is an intentional "feature" or an overlooked bug, but I've noticed it on several OS versions.

[Guest.Visitor]

SQL Search Using Wild Cards

Would using the trim function to remove the trailing spaces work for you?

trim(name) like 'JO%EPH'

Mark

[J.Wells]

SQL Search Using Wild Cards

Mark, Thanks for the reply, and yes, this method works too! Very interesting. So now (thinking out loud, or rather in writing!) , I wonder which is more effective - name like 'JO%EPH%' or trim(name) like 'JO%EPH' or does it even matter? Thanks, Joe

[Guest.Visitor]

SQL Search Using Wild Cards

If name contained the following values: JOSEPH JOSEPHUS Then name like 'JO%EPH%' would select both. trim(name) like 'JO%EPH' would select only 'JOSEPH'. So if this is your situation, then trim is probably the way to go. If not, then both methods work the same, although one may perform better than the other. Note that trim removes both leading and trailing spaces. If you're interested in just removing trailing spaces, use rtrim(name). Mark

[J.Wells]

SQL Search Using Wild Cards

I know I can perform a simple SQL search like this - select * from MYFILE where MYNAME like 'JO%' and the result will be anyone with a name that starts with "JO". I would like to perform a similar search where the middle of the string is missing. Something like this (I know this is the wrong syntax) - select * from MYFILE where MYNAME like 'JO%SEPH' This should return any name starting with "JO" and ending with "SEPH". So, my question is - How do I do this? The SQL book seems to indicate that an underscore would do the trick, but I cannot make it happen. Does anyone know how to do this? Thanks, Joe