Subject Re: The POSITION
Author m_theologos
--- In, Jim Starkey <jas@...>

> Sorry about Claudio, sometimes he's just that way.

Thank you very much Jim. But don't bother. I'm worse than him. And I
respect him very much for what he has done for FB (since IB days).
All of us are just humans.

> This is exactly the right place to discuss things like this. The
> step in any design is to understand the requirements -- what a
> is for, what it is supposed to do, what need it fits, how people
> use, and whether it should conform, extend, or violate the standard.
> So pay no attention to Claudio, this is the right time and place to
> the discussion. A proposal of semantics and syntax is the next

POSITION(X1 IN X2) - of course. (In fact I don't like this, I like it
more with commas ie. Pos(substr, str) is easier to parse and write,
but if the SQL standards say so...)

> First, we need to understand what you want and why.


POSITION('Star' in 'Starkey') -> 1
POSITION('Planet' in 'Starkey') -> 0
POSITION('key' in 'Starkey') -> 5
POSITION(<null> in <whatever>) -> null
POSITION(<whatever> in <null>) -> null
POSITION('STAR' in 'Starkey') -> 1

(...pretty clear I think...)

Extensions from standard:
- Case sensitivity
- Wildcard support

- It's a standard feature (SQL1992 has it) which is missing from FB
- You have already the Knuth-Morris-Pratt implemented for pattern
matching (instead of returning a boolean return a number - I didn't
look at the code but perhaps this is already the case).
- It breaks the way for server side string processing (ie. after this
can be implemented functions like StringReplace, StringStuff,
GetWord(aStr, aDelim, aFromIndex), StrDelete(aStr, aFromIndex), not
necessarily because the above functions are based on it but POSITION
as probably imagine adds much functionality on these ones). And as
you know better than me, strings are, from the data types which needs
processing, the most costly ones on the wire, so, if we manage to
process them on the server then will be a global performance gain,


m. Th.

> --
> Jim Starkey
> Netfrastructure, Inc.
> 978 526-1376