Case Insensitive SELECT

Martyn Bowis

unread,

Mar 26, 2020, 7:30:05 PM3/26/20

to gsql-users

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 :-)

Hall, Steven

unread,

Mar 26, 2020, 7:53:02 PM3/26/20

to Martyn Bowis, gsql-users

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

--
Welcome to GSQL-user group.
- our mission is bringing the power of graph databases to everyone www.opengsql.org
- technical resource can be found here https://docs.tigergraph.com/
---
You received this message because you are subscribed to the Google Groups "gsql-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gsql-users+...@opengsql.org.
To view this discussion on the web visit https://groups.google.com/a/opengsql.org/d/msgid/gsql-users/5702406e-f580-4d25-9355-6a237817a0b4%40opengsql.org.

Martyn Bowis

unread,

Mar 26, 2020, 8:05:42 PM3/26/20

to gsql-users, marty...@gmail.com

Thanks :-) 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 :-)

To unsubscribe from this group and stop receiving emails from it, send an email to gsql-...@opengsql.org.

Szilard Barany

unread,

Mar 27, 2020, 10:04:26 AM3/27/20

to gsql-users, marty...@gmail.com

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

Martyn Bowis

unread,

Mar 27, 2020, 5:30:07 PM3/27/20

to gsql-users, marty...@gmail.com

Hey Szilard,

Thanks for taking a look at this for me :-) 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

Reply all

Reply to author

Forward