Parameterized query and LIKE %

SQL++
1

i have a query where i check if an email address exists as user types it in and start to narrow down the list.
The issue is the % which i don’t want to send down from client.

select DISTINCT e.address from Contacts C
UNNEST C.emails e
where C._type = ‘contact’ and e.address like ‘al%’

the above N1QL query works as expected but how do i add a Parameter + wild Card , the below does not work and i tried a bunch of different approaches

select DISTINCT e.address from Contacts C
UNNEST C.emails e
where C._type = ‘contact’ and e.address like $1%’

2

When people say I want to query with wildcards and/or LIKE I think FTS.
Did you try using Couchbase FTS indexes?

3

You append % in the client in $1

CREATE INDEX ix1 ON Contacts (ALL ARRAY email.address FOR email IN emails END) WHERE _type = "contact";

\set -$eid "al%"

SELECT  DISTINCT email.address
FROM Contacts  AS C
UNNEST C.emails  AS email 
WHERE C._type = "contact" AND email.address LIKE $eid;
4

Yeah that’s what i ended up doing so the the general answer is there is no way co concatenate in the actual
query string a place holder like $1 and the “%”. If so would have been much cleaner then passing $1 + “%” as
the variable

5

If you can do one of these if you want
concat($1,"%") OR $1|| “%”