Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 18beta1 (Debian 18~beta1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


create table t 
(
  label TEXT, 
  should TEXT
);

CREATE TABLE


INSERT INTO t VALUES
('Thomas Hawk AQWS456', 'OK'),
('Cecile Star RQWS456', 'KO'),
('Mickey Mouse WQWS456', 'KO'),
('Donald Duck SQWS456', 'OK');

INSERT 0 4


--
-- Guillaume's result
--

SELECT
  *, 
  CASE 
    WHEN label ~ '\s[A-QS-VX-Za-qs-vx-z][A-Za-z]{3}\d{3}(?:\s|$)' 
      THEN 'OK' 
    ELSE 'KO' 
  END AS result 
FROM t;


label
should
result


Thomas Hawk AQWS456
OK
OK

Cecile Star RQWS456
KO
KO

Mickey Mouse WQWS456
KO
KO

Donald Duck SQWS456
OK
OK



SELECT 4


SELECT
  label,
  REVERSE(label),
  SPLIT_PART(REVERSE(label), ' ', 1),
  RIGHT(SPLIT_PART(REVERSE(label), ' ', 1), 1),
  CASE
    WHEN RIGHT(SPLIT_PART(REVERSE(label), ' ', 1), 1) IN ('R', 'W')  
      THEN 'KO'
    ELSE 'OK'
  END AS result,
  should
FROM
  t;


label
reverse
split_part
right
result
should


Thomas Hawk AQWS456
654SWQA kwaH samohT
654SWQA
A
OK
OK

Cecile Star RQWS456
654SWQR ratS eliceC
654SWQR
R
KO
KO

Mickey Mouse WQWS456
654SWQW esuoM yekciM
654SWQW
W
KO
KO

Donald Duck SQWS456
654SWQS kcuD dlanoD
654SWQS
S
OK
OK



SELECT 4


CREATE OR REPLACE FUNCTION random_pick(arr TEXT[])
  RETURNS TEXT
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
  SELECT (arr)[trunc((random() * (CARDINALITY($1)) + 1))::int];
$func$;

CREATE FUNCTION


--
-- test, check if function is working - 25 records
--

WITH words_1 AS
(
  SELECT 
    ARRAY['Thomas', 'Hawk', 'Cecile', 'Star', 'Mickey', 'Mouse', 'Donald', 'Duck']
      AS w1
),
words_2 AS
(
  SELECT 
    ARRAY['SQWS456', 'AQWS456', 'RQWS456', 'WQWS456'] AS w2
), t AS
(
SELECT
  random_pick((SELECT w1 FROM words_1)) || ' ' ||
  random_pick((SELECT w1 FROM words_1)) || ' ' ||
  random_pick((SELECT w2 FROM words_2)) AS label
FROM 
  GENERATE_SERIES(1,25)
)
SELECT
  *,
  CASE
    WHEN RIGHT(SPLIT_PART(REVERSE(t.label), ' ', 1), 1) IN ('R', 'W')  
      THEN 'KO'
    ELSE 'OK'
  END AS result
FROM
  t;


label
result


Donald Hawk RQWS456
KO

Mouse Hawk RQWS456
KO

Mickey Star SQWS456
OK

Duck Thomas SQWS456
OK

Star Donald AQWS456
OK

Thomas Star SQWS456
OK

Thomas Star AQWS456
OK

Mickey Mickey AQWS456
OK

Hawk Hawk AQWS456
OK

Duck Thomas SQWS456
OK

Hawk Cecile WQWS456
KO

Star Hawk WQWS456
KO

Donald Thomas WQWS456
KO

Duck Star AQWS456
OK

Star Hawk WQWS456
KO

Star Cecile RQWS456
KO

Hawk Duck SQWS456
OK

Mickey Star AQWS456
OK

Mouse Star SQWS456
OK

Mickey Cecile SQWS456
OK

Thomas Star RQWS456
KO

Thomas Thomas RQWS456
KO

Hawk Duck RQWS456
KO

Cecile Mickey RQWS456
KO

Duck Mickey SQWS456
OK



SELECT 25


CREATE TABLE lab
(
  label TEXT
);

CREATE TABLE


INSERT INTO lab
WITH words_1 AS
(
  SELECT 
    ARRAY['Thomas', 'Hawk', 'Cecile', 'Star', 'Mickey', 'Mouse', 'Donald', 'Duck']
      AS w1
),
words_2 AS
(
  SELECT 
    ARRAY['SQWS456', 'AQWS456', 'RQWS456', 'WQWS456'] AS w2
)
SELECT
  random_pick((SELECT w1 FROM words_1)) || ' ' ||
  random_pick((SELECT w1 FROM words_1)) || ' ' ||
  random_pick((SELECT w2 FROM words_2)) AS label
FROM 
  GENERATE_SERIES(1, 100_000);

INSERT 0 100000


SELECT * FROM lab LIMIT 10;   -- Just checking that the records are present!


label


Thomas Mickey RQWS456

Cecile Mouse WQWS456

Mouse Duck RQWS456

Hawk Star WQWS456

Mickey Star RQWS456

Hawk Mickey WQWS456

Mickey Star AQWS456

Cecile Hawk RQWS456

Star Thomas WQWS456

Duck Donald AQWS456



SELECT 10


EXPLAIN (ANALYZE, VERBOSE)
SELECT
  *,
  CASE
    WHEN RIGHT(SPLIT_PART(REVERSE(lab.label), ' ', 1), 1) IN ('R', 'W')  
      THEN 'KO'
    ELSE 'OK'
  END AS result
FROM
  lab;


QUERY PLAN


Seq Scan on public.lab  (cost=0.00..2369.64 rows=86632 width=64) (actual time=0.014..49.148 rows=100000.00 loops=1)

  Output: label, CASE WHEN ("right"(split_part(reverse(label), ' '::text, 1), 1) = ANY ('{R,W}'::text[])) THEN 'KO'::text ELSE 'OK'::text END

  Buffers: shared hit=637

Planning Time: 0.028 ms

Execution Time: 53.828 ms



EXPLAIN


EXPLAIN (ANALYZE, VERBOSE)
SELECT 
  *, 
  CASE 
    WHEN label ~ '\s[A-QS-VX-Za-qs-vx-z][A-Za-z]{3}\d{3}(?:\s|$)' 
      THEN 'OK' 
    ELSE 'KO' 
  END AS result 
FROM lab;


QUERY PLAN


Seq Scan on public.lab  (cost=0.00..1719.90 rows=86632 width=64) (actual time=0.022..146.668 rows=100000.00 loops=1)

  Output: label, CASE WHEN (label ~ '\s[A-QS-VX-Za-qs-vx-z][A-Za-z]{3}\d{3}(?:\s|$)'::text) THEN 'OK'::text ELSE 'KO'::text END

  Buffers: shared hit=637

Planning Time: 0.047 ms

Execution Time: 151.740 ms



EXPLAIN

label	should	result
Thomas Hawk AQWS456	OK	OK
Cecile Star RQWS456	KO	KO
Mickey Mouse WQWS456	KO	KO
Donald Duck SQWS456	OK	OK

label	reverse	split_part	right	result	should
Thomas Hawk AQWS456	654SWQA kwaH samohT	654SWQA	A	OK	OK
Cecile Star RQWS456	654SWQR ratS eliceC	654SWQR	R	KO	KO
Mickey Mouse WQWS456	654SWQW esuoM yekciM	654SWQW	W	KO	KO
Donald Duck SQWS456	654SWQS kcuD dlanoD	654SWQS	S	OK	OK

label	result
Donald Hawk RQWS456	KO
Mouse Hawk RQWS456	KO
Mickey Star SQWS456	OK
Duck Thomas SQWS456	OK
Star Donald AQWS456	OK
Thomas Star SQWS456	OK
Thomas Star AQWS456	OK
Mickey Mickey AQWS456	OK
Hawk Hawk AQWS456	OK
Duck Thomas SQWS456	OK
Hawk Cecile WQWS456	KO
Star Hawk WQWS456	KO
Donald Thomas WQWS456	KO
Duck Star AQWS456	OK
Star Hawk WQWS456	KO
Star Cecile RQWS456	KO
Hawk Duck SQWS456	OK
Mickey Star AQWS456	OK
Mouse Star SQWS456	OK
Mickey Cecile SQWS456	OK
Thomas Star RQWS456	KO
Thomas Thomas RQWS456	KO
Hawk Duck RQWS456	KO
Cecile Mickey RQWS456	KO
Duck Mickey SQWS456	OK