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.