|
----- Original Message ----- From: David Johnston <SNIP> >The condition (WHEN) in a case cannot be a set. You have to make the >_expression_ always resolve to a single row/value. >I'd suggest creating a regexp_matches_single(...) function that calls >regexp_matches(...) in a sub-select so that no matches results in null. >You >then need to decide how you want to handle multiple matches. This function >will return a single text[] and so can be used in places where you want >your >match to only and always return a single result (i.e. non-global behavior). Thanks David, I found that if the whole _expression_ is made a sub-select it works: development=# SELECT CASE development-# WHEN (SELECT LENGTH(ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') development(# , ',') development(# ) = LENGTH('12-70510') development(# ) development-# THEN ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') development(# , ',') development-# ELSE '' development-# END AS "12-70510" development-# ; 12-70510 ---------- (1 row) Cheers, George >Note a recent patch was applied yesterday to resolve an ancient >undiscovered >bug related to this kind of query as well. Using the above >function/behavior >will let you avoid that bug as well. >David J. |
