What happened to the POSITION function? "no such function"?

Databases
1

I need to use the POSITION function in REALSQLDatabase in REALBasic 2012r1. It clearly is included in the list of SQL functions in the 2009 RB Language Reference. It apparently should perform exactly the same operation as INSTR in REALBasic or in SQL LIte, that is determine the offset of a substring within a string. I try the following code (to simplify I use AAA to indicate a field, BBB for a table and ‘CCC’ for a constant string):

SELECT [AAA] FROM [BBB] WHERE POSITION ( [AAA] IN ‘CCC’) = 1

This is supposed to list all fields AAA that are substrings at location 1 in string ‘CCC’.

However, the database server cannot recognize this function. It gives the error “no such table: ‘CCC’”. Then when I replace ‘CCC’ with a table name, it indicates that there is no function called POSITION.

Then I try an even simpler SQL expression and again get the error that there is no function called POSITION:

SELECT POSITION( ‘G’ IN ‘EGG’ )

Has anyone used this function? Why is it in the Language Reference if it does not exist. Note that INSTR also does not work. I assume that POSITION was deprecated at some time and replaced by INSTR, but neither seem to work.

2

Is the underlying database now a SQLLite one, as RealSQLdatabase is now obsolete.
If so, INSTR should work
How are you calling that?

I use AAA to indicate a field

so your code might expand to

Select  surname from people where POSITION(forename in 'Jeremy') =1

Do you want to find rows where the entire contents of one field matches the start of your constant string?
usually its the other way around

[quote]'give me rows where the field contains text which begins with ‘Sales’
[/quote]

In which case where [field] like ‘Sales%’

is what you need.
SQLLite uses INSTR

where INSTR(myfield,thesearchvalue) = 1

3

Not sure about position, but it doesn’t look like that was ever a SQLite function. InStr is available in SQLite 3.7.15 or later (using SQLiteDatabase class in Xojo 2013r4.1 or later).

SELECT instr('hello world', 'hello')

https://www.techonthenet.com/sqlite/functions/instr.php
https://sqlite.org/lang_corefunc.html#instr