LIKE with ESCAPE character

GSQL
#1

Hello, I’m trying to retrieve everyone where firstName starts with “TEST_”. Please note ‘_’ is part of the string and not to match any single character. I’m using following query but I’m having error installing this query, did I got the syntax wrong?
Reference: https://docs.tigergraph.com/dev/gsql-ref/querying/operators-functions-and-expressions#escape-escape_char

CREATE OR REPLACE DISTRIBUTED QUERY temp() FOR GRAPH social {
ALL = {Person.*};
OTHER = SELECT s FROM ALL:s WHERE s.firstName LIKE “TEST\_%” ESCAPE “\”;
PRINT OTHER.size();
}

Error: Lexical error at line 1, column 148. Encountered: “\” (92), after : “”

#2

@dhruva Can you try something like this which puts the searched string as a parameter and then is used later:

CREATE QUERY temp(/* Parameters here */) FOR GRAPH myGraph { 
  /* Write query logic here */ 
  
string x ;
x = "%TEST%";
ALL = {v_type.*};
OTHER = SELECT s FROM ALL:s WHERE s.firstName LIKE x;
PRINT OTHER.size();
}
#3

Thank you @Jon_Herke for quick response. Syntax you shared works same was as

OTHER = SELECT s FROM ALL:s WHERE s.firstName LIKE “%TEST%”;

However, I’m looking to match string which starts with TEST_
I have tried like with “TEST\_%” and it performs LIKE operation however it treats ‘_’ as a character that performs single character match. I want it to use ‘_’ as part of the string and not as single character match.
ie., it pulls firstname with value “TEST John”. My use case is to only pull firstname if it looks like “TEST_Jane S”

When I use “ESCAPE”, I get Lexical error.

#4

@dhruva Maybe try the following:

CREATE QUERY temp(/* Parameters here */) FOR GRAPH myGraph { 
  /* Write query logic here */ 
  
string x ;
string y;
string z;
x = "TEST";
y = "\_%";
z = x + y;
ALL = {v_type.*};
OTHER = SELECT s FROM ALL:s WHERE s.firstName LIKE z;
PRINT OTHER;
}

image
image978×554 10.3 KB

#5

I also had “TEST Jane” but that wasn’t returned

image
image1992×328 31.5 KB

#6

@Jon_Herke I tried version with string x,y,z per suggestion but similar results. It pulls both “TEST John” and “TEST_Jane S”

#7

@dhruva Very Odd. I tried adding more data in my sample solution and it was able to pick up the new ones and disregard those without “_”. What version of TigerGraph are you using? Maybe we can jump into a Zoom session to see it?

Sample Data:

image
image1306×862 52.3 KB

Results:
image
image1172×448 11.2 KB

#8

Yeah sure I’m avail for a quick call. We are on TG. v3.1.0.

#9

Hello @dhruva
Thank you for bringing this issue into our attention. With regards to specifying the escape character in LIKE expression, there is actually a typo in the examples given in the reference you mentioned. Escape characters should in fact be enclosed within single quotes instead of double quotes. This is why this query resulted in a lexical error.

Therefore, we suggest changing the WHERE clause to:
WHERE s.firstName LIKE "TEST\_%" ESCAPLE '\'

We will also update the typo on our end.

1 Like
#10

@Adil_Ainihaer Unfortunately I got same lexical error using

WHERE s.firstName LIKE “TEST\_%” ESCAPE ‘\’

1 Like
#11

@dhruva Is it exactly the same? Could you share the error message?

1 Like
#12

@Adil_Ainihaer
Error Message: Lexical error at line 1, column 336. Encountered: “\” (92), after : “”

Line and column numbers might look off since I’m using gsql terminal to install the query. I generally use graph studio however when I use WHERE s.firstName LIKE “TEST\_%” ESCAPE ‘\’ - it doesn’t give me error in the console log screen but when I try to install it it gives error popup:

No query to install.
Coding errors of following queries need to be fixed before installing them:

  • temp
1 Like
#13

Thank you for the quick follow-up. It turned out that the character '\' is mishandled in our parser for ESCAPE. So the current workaround is to use a different escape character instead. In my original message I only noticed the use of double-quote and did not notice the mishandling of '\'. And in this specific case the errors would appear to be similar.

2 Likes