Case Insensitive SELECT

GSQL
#1

Is there a way to do a case-insensitive SELECT please?

I would like to make the following query case-insensitive

CREATE QUERY getAllPeopleByKeyword(STRING keyword) FOR GRAPH MyGraph {

STRING name = “”;

STRING wildcard = “%”;

name = wildcard + keyword + wildcard;

arrPeople = {people.*};

arrPeopleFiltered = SELECT v FROM arrPeople:v

WHERE v.name LIKE name;

PRINT arrPeopleFiltered;

}

As it stands, entering the keyword “Jo” will return Joe and John, etc.

However, entering the keyword “jo” returns nothing, as the SELECT is case-sensitive.

How to make this case-insensitive please?

Thanks :slight_smile:

#2

Hi Martyn

Have you considered coercing the both sides of the WHERE expression to upper or lower case using the GSQL upper(string) or lower(string) function

https://docs.tigergraph.com/dev/gsql-ref/querying/operators-functions-and-expressions see String functions

Steve

#3

Thanks :slight_smile: I was trying to find lower and upper.

However, I have tried the following, and do not get a result, even when using the same case as that which I am searching for:

CREATE QUERY getAllPeopleByKeyword(STRING keyword) FOR GRAPH MyGraph {

STRING name = "";
STRING wildcard = "%";
name = wildcard + lower(keyword) + wildcard;

arrPeople = {people.*}; # initialized with all vertices of type 'people'

arrPeopleFiltered = SELECT v FROM arrPeople:v
WHERE lower(v.name) LIKE name; 
PRINT arrPeopleFiltered;

}

The query does not show an error in GraphStudio, however, there is something incorrect. What might it be please?

Thanks :slight_smile:

#4

Hi Martyn,

Your (updated) code runs as expected for me.

Used this minimal schema:

DROP ALL
CREATE VERTEX Person (PRIMARY_ID id INT, name STRING, age INT)
CREATE GRAPH MyGraph (*)

Loaded this data set via Studio:

1,Thorin,195
2,Fili,82
3,Kili,77
4,Oín,167
5,Glóin,158
6,Balin,178
7,Dwalin,169
8,Ori,76
9,Dori,114
10,Nori,104
11,Bifur,127
12,Bofur,98
13,Bombur,107
14,Bilbo,50
15,Gandalf,5000

Installed your query (only changed the vertex name):

CREATE QUERY getAllPeopleByKeyword(STRING keyword) FOR GRAPH MyGraph {

STRING name = "";
STRING wildcard = "%";
name = wildcard + lower(keyword) + wildcard;

arrPeople = {Person.*}; # initialized with all vertices of type 'people'

arrPeopleFiltered =
    SELECT v FROM arrPeople:v
    WHERE  lower(v.name) LIKE name;

PRINT arrPeopleFiltered;

}

And it runs as expected in GSQL:

GSQL > RUN QUERY getAllPeopleByKeyword(“LI”)

{

“error”: false,

“message”: “”,

“version”: {

"schema": 0,

"edition": "enterprise",

"api": "v2"

},

“results”: [{“arrPeopleFiltered”: [

{

  "v_id": "2",

  "attributes": {

    "name": "Fili",

    "age": 82

  },

  "v_type": "Person"

},

{

  "v_id": "6",

  "attributes": {

    "name": "Balin",

    "age": 178

  },

  "v_type": "Person"

},

{

  "v_id": "3",

  "attributes": {

    "name": "Kili",

    "age": 77

  },

  "v_type": "Person"

},

{

  "v_id": "7",

  "attributes": {

    "name": "Dwalin",

    "age": 169

  },

  "v_type": "Person"

}

]}]

}

GSQL > RUN QUERY getAllPeopleByKeyword(“li”)

{

“error”: false,

“message”: “”,

“version”: {

"schema": 0,

"edition": "enterprise",

"api": "v2"

},

“results”: [{“arrPeopleFiltered”: [

{

  "v_id": "2",

  "attributes": {

    "name": "Fili",

    "age": 82

  },

  "v_type": "Person"

},

{

  "v_id": "6",

  "attributes": {

    "name": "Balin",

    "age": 178

  },

  "v_type": "Person"

},

{

  "v_id": "3",

  "attributes": {

    "name": "Kili",

    "age": 77

  },

  "v_type": "Person"

},

{

  "v_id": "7",

  "attributes": {

    "name": "Dwalin",

    "age": 169

  },

  "v_type": "Person"

}

]}]

…and in Studio as well.

Best regards,

Szilard

On Friday, March 27, 2020 at 12:05:42 AM UTC, Martyn Bowis wrote:
Thanks :slight_smile: I was trying to find lower and upper.

However, I have tried the following, and do not get a result, even when using the same case as that which I am searching for:

CREATE QUERY getAllPeopleByKeyword(STRING keyword) FOR GRAPH MyGraph {

STRING name = "";
STRING wildcard = "%";
name = wildcard + lower(keyword) + wildcard;

arrPeople = {people.*}; # initialized with all vertices of type 'people'

arrPeopleFiltered = SELECT v FROM arrPeople:v
WHERE lower(v.name) LIKE name; 
PRINT arrPeopleFiltered;

}

The query does not show an error in GraphStudio, however, there is something incorrect. What might it be please?

Thanks :slight_smile:

#5

Hey Szilard,

Thanks for taking a look at this for me :slight_smile: I tried running it again just now and yes, it does work. Not sure why it did not seem to work yesterday? Anyways, this code does work, as you have also verified.

Cheers,
Martyn