Custom where clause with LIKE operator

Hi,

I’m trying a custom query where param1 is a string and value1 is a column in a database table.
Currently if value one is username, it is being read in as the string “username” rather than referencing the column ‘username’.
How do I. correctly write this query?

  • Innocent.

Hello, @Innocent_Nwaukwa.

Have you tried doing it without first the quotes?

Best Regards, Nikita.

Hey @Nikita_Fedorishchev,

Yeah, but then it says param1 isn’t a column.
We’ve since found a workaround but I would like to know how to get value1 to be recognized as a column rather than a string literal.

Innocent.

value1 must be a value per the syntax of the where clause that uses the LIKE operator:

COLUMNNAME LIKE `%literal string value%`

The issue is, I’m trying to pass a string and find all entries in the db that the provided string contains.

Hi @Innocent_Nwaukwa

Let me know if I’m wrong, but it seems that you want to make a query that should check if a specified string exists in data stored in specific columns.
If yes, then perhaps you want a query like this:
'value' IN (columnA, columnB)

You can also rewrite this query as:
columnA LIKE 'value' OR columnB LIKE 'value'

If you are looking for substrings in the column, then you can use the following:
columnA LIKE '%value%' OR columnB LIKE '%value%'

Regards,
Viktor

Unfortunately, no.

I’m trying to see if a value contains any entry at all in a specific column columnA.
Like this:

.

NOTE: My team and I have since found a different solution where we have a hardcoded list in a custom function with some regex for the check but I’m just curious if this db query is possible.

1 Like

I am having hard time understanding the purpose of the ‘gberlin’ string in the query you showed. The standard SQL syntax requires column name in that place, for example, see here:

Yeah, seems like I might be looking for a hybrid solution here.
But with the ‘gberlin’, I’m trying to see if there’s any entry in the database under the “City” column that ‘gberlin’ contains.

I know how LIKE works. was just curious if it was possible to do it this way.
Thought I might be able to since I got the result I wanted in an online sql executor but yeah, I know it’s not how the LIKE query is typically used.

How different is it from this:?

SELECT * FROM Customer WHERE City LIKE '%gberlin%'

The difference is,

SELECT * FROM Customer WHERE ‘gberlin’ LIKE ‘%’ + City + ‘%’
will check to see if ‘gberlin’ contains an entry in the city column of the table which is that one row that has ‘Berlin’ as its city.

Whereas,

SELECT * FROM Customer WHERE City LIKE ‘%gberlin%’
will check to see if an entry in the City column contains ‘gberlin’.

hello @Innocent_Nwaukwa

looks like I understand what you looking. You what something like the following:

SELECT * FROM Customer WHERE 'gberlin' LIKE concat( '%', City, '%')

And in backendless the query should be with the following where clause:

'gberlin' LIKE concat( '%', City, '%')

But the issue is that Backendless supports only literals as arguments concat function

Ah, that makes sense.
I’ll keep that in mind with my future queries.
Thank you so much everyone for bearing with me :sweat_smile: