By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,661 Members | 781 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,661 IT Pros & Developers. It's quick & easy.

PostgreSQL equivalent to MySQL LOCATE()

P: 1
Hi

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

Expand|Select|Wrap|Line Numbers
  1. $query="SELECT * FROM table WHERE LOCATE('".$var."', field)> 0 ORDER BY LOCATE('".$var."', field) LIMIT 10";
Any Ideas?
Sep 19 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 700
Hi

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

Expand|Select|Wrap|Line Numbers
  1. $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.
Sep 20 '07 #2

P: n/a
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('substring','long string containing substring')
is equivalent to
PostgreSQL position('substring' IN 'long string containing substring')
Oct 17 '10 #3

P: 1
I am having a similar problem.

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

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

Thanks.
Dec 13 '10 #4

Expert 100+
P: 700
You can use
Expand|Select|Wrap|Line Numbers
  1. strpos(string, substring)
  2.  
but it takes only two arguments.

You can use strpos with substr and it works lile locate
Expand|Select|Wrap|Line Numbers
  1. locate(to_find,str,pos)=strpos(substr(str,pos),to_find)
  2.  
Dec 14 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.