Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » postgresql error using lower/upper/ilike functions (Case insensitive searching postgresql)
postgresql error using lower/upper/ilike functions [message #6186] Sat, 04 March 2017 22:43 Go to next message
renato is currently offline  renato
Messages: 4
Registered: October 2016
Junior Member
Hi Tony,

I'm trying to search case insensitive in PostgreSQL. Postgresql doesn't allow you to make text searching case insensitive.
You have to resort to either citext or lower/upper functions in your query. I don't like any of the options.
Radicore isn't allowing the syntax of postgresql lower function in combination with a LIKE.
It also doesn't recognize the 'ILike' operator.

The following throws a "Cannot extract token from:'%hello%'" error:
$where = "lower(product_name) LIKE '%hello%'";

'product_name' is a column in the database.
Seems to happen in the where2indexArray function from include.library.inc

It does however work with the '=' operator.
$where = "lower(product_name) = '%hello%'";

Of course the wild cards wouldn't work.

Tree questions:
1. How can I fix this?
2. Is there another way I can do case insensitive searching on texts in Radicore with pgsql?
3. Is there a way to hook into the default search to change the query to wrap the column name with lowar?

Re: postgresql error using lower/upper/ilike functions [message #6187 is a reply to message #6186] Sun, 05 March 2017 05:43 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2028
Registered: April 2006
Location: Surrey, UK
Senior Member
I have amended include.library.inc so that it will allow both of the following in the WHERE string:
lower(product_name) LIKE '%hello%'"
product_name ILIKE '%hello%'


[Updated on: Mon, 06 March 2017 03:40]

Report message to a moderator

Re: postgresql error using lower/upper/ilike functions [message #6189 is a reply to message #6187] Mon, 06 March 2017 12:39 Go to previous message
renato is currently offline  renato
Messages: 4
Registered: October 2016
Junior Member
Yes. It works!
Thanks!
Previous Topic: Outer Database Popup File Missing
Goto Forum:
  


Current Time: Sun Mar 26 22:44:04 EDT 2017

Total time taken to generate the page: 0.07777 seconds