CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Vojtech
Vojtech

Reputation: 2603

Query with regexp_matches in select returns empty result set

I have following SQL:

SELECT code, name
ARRAY_TO_JSON(regexp_matches(code, '^(.*?)(24)(.*)$','i')) AS match_code,
ARRAY_TO_JSON(regexp_matches(name, '^(.*?)(24)(.*)$','i')) AS match_name
FROM manufacturer
WHERE (code  ~* '^(.*?)(24)(.*)$' OR name  ~* '^(.*?)(24)(.*)$')
ORDER BY name;

There's following record in the table:

code | name
-------------
24   | Item 24

The result of the query is:

code | name    | match_code   | match_name
-------------------------------------------------
24   | Item 24 | ["","24",""] | ["Item ","24",""]

Then I replaced string '24' by 'Item' in the query and I expect this result:

code | name    | match_code   | match_name
-------------------------------------------------
24   | Item 24 | []           | ["", "Item ","24"]

BUT the result is:

Empty result set

Function regexp_matches probably returns no row if it doesn't match.

How can I fix the query so that it returns rows even when regexp_matches doesn't match?

Thanks in advance.

Upvotes: 5

Views: 2174

Answers (1)

redneb
redneb

Reputation: 23870

regexp_matches returns a setof text[], i.e. a table, and it's sometimes confusing to use it as an output expression in SELECT. You can create a subquery so that you can move it to the FROM clause. Try this:

SELECT
    code,
    name,
    coalesce(array_to_json((SELECT * FROM regexp_matches(code, '^(.*?)(24)(.*)$','i'))),'[]') AS match_code,
    coalesce(array_to_json((SELECT * FROM regexp_matches(name, '^(.*?)(24)(.*)$','i'))),'[]') AS match_name
FROM manufacturer
WHERE (code  ~* '^(.*?)(24)(.*)$' OR name  ~* '^(.*?)(24)(.*)$')
ORDER BY name;

Notice that I am also using coalesce to convert NULL (which is what we get from the regexp_matches subquery if there are no matches) to an empty JSON array.

Upvotes: 3

Related Questions