Pattern matching in GridDB using NewSQL

Question

I am looking for pattern matching in GridDB using NewSQL to return values true when matched and false when not matched.

The string can start with

91 followed 33 followed by 8 digit number followed by upper case letters eg 913312345678JAYP (should return true)
0 followed 33 followed by 8 digit number followed by upper case letters eg 03312345458MARYP (should return true)
Starts with 33 followed by 8 digit number followed by upper case letters eg 3312345458TOMH (should return true)

If the string does not start with 91/0/none followed by 33 and 8 digit number and then letters it should return False. Below are some examples:

53312345458RYANH --invalid

3312345458 --invalid

03312345458JAYP8 --invalid

Can anyone help with with solution. I used the function GLOB and it does not have the OR option so that I can choose between 0/91/none.

Can't you just use three glob expressions? glob('33[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]*', your_column) OR glob('033[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]*', your_column) OR glob('9133[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]*', your_column) — MatBailie
– MatBailie, Commented Sep 6 at 7:24
Try using ^(0|91)?33\d{8}[A-Z]+$. Regex 101 - btw why 03312345458JAYP is invalid? — Michał Turczyn
– Michał Turczyn, Commented Sep 6 at 7:36
@MatBailie tried this glob('033[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]*', '03312345458JAYP8') and returns true whereas should be false. The string should end with letters and no numbers in it. — sayana_dutta
– sayana_dutta, Commented Sep 6 at 17:18

Andrei Odegov · Accepted Answer · 2025-09-08 17:23:23Z

0

To satisfy the uppercase string ending condition, you must use a pattern ending in "[A-Z]*[A-Z]". The working solution is as follows:

SELECT
  val,
  ( ( GLOB('33[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', RTRIM(val, cs.cs)) OR
      GLOB('033[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', RTRIM(val, cs.cs)) OR
      GLOB('9133[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', RTRIM(val, cs.cs)) ) AND
    SUBSTR(val, -1, 1) BETWEEN 'A' AND 'Z' )
FROM (SELECT '913312345678JAYP' AS val UNION ALL
      SELECT '03312345458MARYP' UNION ALL
      SELECT '3312345458TOMH' UNION ALL
      SELECT '53312345458RYANH' UNION ALL
      SELECT '3312345458' UNION ALL
      SELECT '03312345458JAYP8') AS t
CROSS JOIN (SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS cs) AS cs;

The result.

+------------------+-------+
| val              |       |
+------------------+-------+
| 03312345458MARYP | true  |
| 53312345458RYANH | false |
| 913312345678JAYP | true  |
| 3312345458       | false |
| 03312345458JAYP8 | false |
| 3312345458TOMH   | true  |
+------------------+-------+

edited Sep 8 at 17:23

answered Sep 7 at 18:56

Andrei Odegov

3,5722 gold badges18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sayana_dutta Sep 8 at 13:34

Hi @Andrei the solution works for all the cases but for 3312345458 it should return False as after the digits it should just have letter.

Andrei Odegov Sep 8 at 17:34

I have corrected my answer. The fragment SUBSTR(val, -1, 1) BETWEEN 'A' AND 'Z' can be replaced with GLOB('*[A-Z]', val).

Collectives™ on Stack Overflow

Pattern matching in GridDB using NewSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related