Problem using strpos in a select statement

alalchan

Hi,

Can anyone help me with this statement I am trying to query a Access database using the following code but I get an SQL error Syntax error (missing operator) in query expression. Is it possible to use strpos in a SQL statement?

pro_id is a field in the database whose value is 489

$valves = 489;

$SQLQuery = "SELECT * FROM Products WHERE strpos(".$valves.",pro_id,1) !== 0";

Sxooter

I think you're looking for position(). See:

SQL In a Nutshell: Functions

alalchan

Thanks for your reply, I tried using the POSITION statment but still get an error this is what I replaced it with....

$SQLQuery = "SELECT * FROM Products WHERE POSITION('ab' IN 'abcde') !== 0";

error says....

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'POSITION('ab' IN 'abcde') !== 0'., SQL state 37000 in SQLExecDirect

Sxooter

Are you sure the !== operator is a legal mysql operator?

I would think changing it to

SELECT * FROM Products WHERE POSITION('ab' IN 'abcde') <> 0

<> is the SQL not equals operator. Don't go expecting SQL to do anything in any way shape or form the same as C or PHP. It hardly ever does.

Roger_Ramjet

And don't expect MSAccess functions and syntax to resemble standard SQL in any shape or form either, cos it rarely does.

The Access equivalent of POSITION is InStr([start, ]string1, string2[, compare])

It returns position of first occurence using base 1 not 0: 0 is used to indicate no match found.

$SQLQuery = "SELECT * FROM Products WHERE InStr('ab', 'abcde', 1) >0";

This would return only those where 'ab' was found. The compare switch is for binary or text comparison, in this case I've set it to text - not case-sensitive.

Sxooter

Wow. There are times I'm really glad Microsoft does NOT make a version of Access for Linux environments...

alalchan

Thanks for your help, yes the instr worked.

Cheers

Roger_Ramjet

InStr is also the Oracle function - and probably came first knowing Oracle.

Roger_Ramjet

Sxooter wrote:
Wow. There are times I'm really glad Microsoft does NOT make a version of Access for Linux environments...

My heart agrees - but my head sez "There would be that much more work for you if there were." being as I've been programming the beast for 10 years.

And it still amazes me with it's confounding behaviour at least once a month. :glare: