CODEWITHSUNDEEP
sqlpostgresql
cxc
cxc

Reputation: 259

Is there an escape character that does not match anything in Postgresql?

Is there an escape character in Postgres that is "opposite" to %? When I use where my_column like '%' will return all the records. But is there an operator that does not match anything? As in where my_column like MAGIC_OPERATOR that will return none of the records? I am calling a stored procedure from the C# code and this is the situation that I am in:

My data:

GRADES_TABLE
--------------------------------------------------
          Math     |   History   |   English
Alice      A       |    NULL     |      B
Bob       NULL     |     A       |      B
Charlie    C       |     B       |     NULL

My function:

CREAT FUNCTION GetStudents(MathGrade CITEXT, HistGrade CITEXT, EngGrade CITEXT)
RETURNS TABLE (Student CITEXT)
AS $$
BEGIN
RETURN QUERY
    SELECT STUDENT FROM GRADES_TABLE WHERE 
        (COALESCE(Math, '') LIKE MathGrade
         OR COALESCE(History, '') LIKE HistGrade
         OR COALESCE(English, '') LIKE EngGrade
END $$

My C# code calling the stored procedure:

GetStudents("%", "%", "%") --> This should return Alice, Bob, and Charlie
GetStudents("A", "", "") --> This should return Alice
GetStudents("A", "A", "") --> This should return Alice and Bob

Since I have NULL values in the table so I have to use COALESCE to make them empty strings so they can be found by like '%'. But this way, all three calls in the C# code return Alice, Bob, and Charlie. It will be the same result if I pass in empty string like GetStudents("A", "", ""). So I am wondering if there is any special escape character that does not match anything? Obviously, I can pass in some garbage value GetStudents("A", "IMPOSSIBLE_GRADES", "IMPOSSIBLE_GRADES") and this will get me the desired result. But just wondering if there is any proper way to achieve this? Suggestions for making changes in the Postgres function or in the C# code are welcome as well. Thanks!

Upvotes: 1

Views: 681

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use:

where my_column like null

In most cases, you could also use regular expressions:

where my_column ~ '^$'

Or:

where my_column like ''

The latter two only match empty strings.

Upvotes: 1

Related Questions