Postgres Pro
Downloads
Home   >   mailing lists

Thread: replace_matches does not return {null}

replace_matches does not return {null}

From
Andreas Gaab
Date:
<div class="WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Hi,</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Itried to order a text-column only by parts of the entries. Therefore I used regexp_matches(), but
unfortunatelyI am loosing rows.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">SELECT regexp_matches('abc','[0-9]+'), 
regexp_matches('123','[0-9]+');</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times
NewRoman","serif""> </span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Doesnot return “{null}, {123}” but no result at all.</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Is this behavior expected? How can I work
around?</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Iam running postgres 8.4</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Andreas</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">SCANLAB AG</span><pclass="MsoNormal"><span
style="font-size:10.0pt">Dr. Andreas Simon Gaab</span><pclass="MsoNormal"><span style="font-size:10.0pt">Entwicklung •
R & D</span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">Siemensstr. 2a• 82178 Puchheim • Germany</span><p class="MsoNormal"><span
style="font-size:10.0pt">Tel. +49 (89) 800 746-513• Fax +49 (89) 800 746-199</span><p class="MsoNormal"><span
style="font-size:10.0pt"><ahref="mailto:a.gaab@scanlab.de">mailto:a.gaab@scanlab.de</a> • <a
href="http://www.scanlab.de">www.scanlab.de</a></span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Vorstand: Georg Hofner (Sprecher), Christian Huttenloher,
Norbert Petschik</span><pclass="MsoNormal"><span style="font-size:10.0pt">Aufsichtsrat (Vorsitz):
Dr. Hans J. Langer</span><pclass="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""></span><pclass="MsoNormal"> </div>

Re: replace_matches does not return {null}

From
Tom Lane
Date:
Andreas Gaab <A.Gaab@scanlab.de> writes:
> I tried to order a text-column only by parts of the entries. Therefore I used regexp_matches(), but unfortunately I
amloosing rows.
 

> SELECT regexp_matches('abc','[0-9]+'),  regexp_matches('123','[0-9]+');

> Does not return "{null}, {123}" but no result at all.

Yes, because regexp_matches returns a rowset of zero or more results.
The fine manual suggests putting it in a sub-select if what you want
is a null or a single result:
 SELECT ... , (SELECT regexp_matches(...)) FROM ...
        regards, tom lane