Is this a Regular Expression?

[Former Member]

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)

[Former Member]

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.

[Former Member]

WOW! Many thanks to both of you!

The snippet comes from a query that showed up in a client's database trace as a time-consuming one. The SQL comes from a PowerBuilder application, so it could be created in a DataWindow or it could be explicitly coded, and I suppose it could be followed by an EXECUTE IMMEDIATE as Mark suggests. I don't have access to the application code, so I can't say for sure.

But since no index would be used for this part of the query, and several other search conditions in the same query use columns that have no index on them either, I'm thinking that a portion of the performance problem might be right here.

[MarkCulp]

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.