PostgreSQL equivalent to MySQL LOCATE()

[zerogreen]

Hi

I am looking for an equivalent to the LOCATE function from MySQL.
I have a query(PHP) that looks like this:

$query="SELECT * FROM table WHERE LOCATE('".$var."', field)> 0 ORDER BY LOCATE('".$var."', field) LIMIT 10";

Any Ideas?

[rski]

Hi

I am looking for an equivalent to the LOCATE function from MySQL.
I have a query(PHP) that looks like this:

$query="SELECT * FROM table WHERE LOCATE('".$var."', field)> 0 ORDER BY LOCATE('".$var."', field) LIMIT 10";

Any Ideas?

I do not know mysql, but does LOCATE work as regular expressions. If so u can use postgres LIKE (or ILIKE) statement.

[penguintamer]

This may be a little late, but if I got here from a Google search, other people will, too. So here's an answer:

MySQL locate('substri ng','long string containing substring')
is equivalent to
PostgreSQL position('subst ring' IN 'long string containing substring')

[Adam Potter]

I am having a similar problem.

However I wish to use this form:
LOCATE(substr,s tr,pos)

Can someone offer me a way too do this in postgresql?
(as 'position' does not contain the 'pos' function)

Thanks.

[rski]

You can use

strpos(string, substring)

but it takes only two arguments.

You can use strpos with substr and it works lile locate

locate(to_find,str,pos)=strpos(substr(str,pos),to_find)