Is this a Regular Expression?

[#1]

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.