Or are the little hats just treated as text characters because it's a LOCATE function and not a SIMILAR TO or REGEXP or REGEXP_SUBSTR ?
snippet from a WHERE clause....
AND ( LOCATE ( ''^xxxyy^'' , ''^'' + table.column + ''^'' ) > 0 OR ''^xxxyy^'' ='''' )
How am I meant to interpret the second part of it? (The ''^xxxyy^'' ='''' part)
asked 24 May '11, 18:31
The predicates that you have given are not regular expressions by merely a normal text locate search ( LOCATE ( ''^xxxyy^'' , ''^'' + table.column + ''^'' ) > 0 ) and text string equality test ( ''^xxxyy^'' ='''' ).
Note that what appears to be double quotes are in fact double single quotes. This leads me to believe that you have copied your code snippet from a piece of code that is composing an expression that will be used in an EXECUTE IMMEDIATE !
If we take the code snippet at its face value (and ignoring the fact in the previous paragraph), the '^xxxyy^' = '' predicate will always evaluate to FALSE so it is of little use in the predicate evaluation.... BUT ...
The LOCATE( ''^xxxyy^'' , ''^'' + table.column + ''^'' ) > 0 predicate makes me believe that the xxxyy string is going to be replaced with a "real value" before the expression is evaluated by the EXECUTE IMMEDIATE. Assuming this is true, then it would imply that the LOCATE expression is checking to see if the named column matches the replacement string.
It may be helpful if you were to either provide more context or show us the full piece of code.
answered 24 May '11, 19:30
The carets (^) are not treated as special characters in the LOCATE() function. If table.column does not have any carets in the column values, then the expression you list is almost* equivalent to "table.column ='xxxyy'" except that an index will not be used to answer this query.
The second part of the condition evaluates to FALSE (and the OR will be simplified to merely the first condition).
Without knowing the structure of the data it is hard to know why the search condition is coded this way. If the constant literal '^xxxyy^' is being generated by software, then the second condition will "turn off" the search condition if an empty string is passed in.
In the above, I said that the LOCATE() condition is almost equivalent to "table.column='xxxyy'". Depending on the character collation used, LOCATE() may give different answers for some comparisons:
select unistr('\\u00DF') S, N'ss' ss , if S=ss then 'eq' else 'neq' endif iseq , locate( ss, S ) loc
The above query returns: ('ß','ss','eq',0)
The LOCATE() function uses character-by-character comparisons (as does LIKE and some other functions), while the equality operator uses string-by-string comparisons.