Sameer Kumar wrote > On Thu, Jan 9, 2014 at 1:26 AM, George Weaver < > gweaver@ > > wrote: > >> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') >> development(# , ',') > > > I guess this part of your statement will return 1,2, which is a set > > Can you try below: > SELECT CASE > WHEN LENGTH(ARRAY_TO_STRING( > REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') > , ',') > ) > = LENGTH('12-70510') > > THEN cast(ARRAY_TO_STRING( > REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') > , ',') as varchar(100)) > > ELSE '' > END AS "12-70510"; > > But anyways, I think the best way to do it is the way you have already > figured (check the plan for both statements once you have sorted out the > error) > > > Best Regards, > *Sameer Kumar | Database Consultant* > > *ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore > 069533 > M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com > www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz > > [image: email patch] > > This email may contain confidential, privileged or copyright material and > is solely for the use of the intended recipient(s). > > > image002.jpg (7K) > <http://postgresql.1045698.n5.nabble.com/attachment/5786031/0/image002.jpg> 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). 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. -- View this message in context: http://postgresql.1045698.n5.nabble.com/argument-of-CASE-WHEN-must-not-return-a-set-tp5785874p5786085.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general