Thread

1. Select where (upper(xy)~'.CH'); ..matches also SPACE CH

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-10-26T04:29:49Z

   Marcel (marcel@simmcomm.ch) reports a bug with a severity of 3
   The lower the number the more severe it is.
   
   Short Description
   Select where (upper(xy)~'.CH'); ..matches also SPACE CH
   
   Long Description
   There exists a problem with the ~ statement. The codesample and the text is from an adult contact database.
   
   The compare string .CH matches the word 'EINEN CHANCE' in the sentence. Seems to be, that the . will match the space between the words. This doesn't happen, if you replace .CH with _CH or something else.
   
   Sample Code
   meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and (upper(adtext)~'.CH');
   upper
   
   --------------------------------------------------------------------------------------------------
   --------------------------------------------------------------------------------------------------
   ---------------------------------------------------------------------------------
   WELCHE DOMINA WILL SICH EINEN SKLAVEN HALTEN. BIN EIN 29-JHRIGER]BI-BOY UND SEHR DEVOT. BITTE GEB
   EN SIE MIR EINEN CHANCE DAMIT ICH IHNEN]BEWEISEN KANN DASS ICH IHR SKLAVE SEIN WERDE UND NUR FR S
   IE BEREIT BIN]ALLES ZU MACHEN WAS SIE VON MIR VERLANGEN.]UNTERTNIGST IHR SKLAVE]
   (1 row)
   
   meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and (upper(adtext)~'_CH');
   
   upper
   -----
   (0 rows)
   
   meetingpoint=>
   
   No file was uploaded with this report
   
   
   

2. Re: Select where (upper(xy)~'.CH'); ..matches also SPACE CH

   D. Jay Newman <jay@sprucegrove.com> — 2000-10-26T14:56:05Z

   >Marcel (marcel@simmcomm.ch) reports a bug with a severity of 3
   >The lower the number the more severe it is.
   >
   >Short Description
   >Select where (upper(xy)~'.CH'); ..matches also SPACE CH
   >
   >Long Description
   >There exists a problem with the ~ statement. The codesample and the text is
   >from an adult contact database.
   >
   >The compare string .CH matches the word 'EINEN CHANCE' in the sentence.
   >Seems to be, that the . will match the space between the words. This
   >doesn't happen, if you replace .CH with _CH or something else.
   
   Strange. The string '.CH' should match any single character directly
   followed by 'CH'. It *should* match '_CH' as well as ' CH'.
   
   If you want the "." to match something, then use the appropriate list
   [a-zA-Z] for alphabetical characters and suchwhat. If you want to match
   the "." character, then use '\.CH'.
   
   Question: why aren't you using ~* (which does a case insensitive match)
   rather than upper(xy) ~ '.CH'? I haven't done benchmarking so I don't
   know which is faster...
   
   I hope this helps.
   -- 
   D. Jay Newman                   ! For the pleasure and the profit it derives
   jay@sprucegrove.com              ! I arrange things, like furniture, and
   http://www.sprucegrove.com/~jay/   ! daffodils, and ...lives.  -- Hello Dolly
   
   

3. Re: Select where (upper(xy)~'.CH'); ..matches also SPACE CH

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-10-26T15:19:47Z

   I think that's what it should be doing.
   ~ is a regexp search, and . is the any character match
   special character. 
   
   If you're looking for an actual . you'll need to 
   double backslash escape it.
   
   Stephan Szabo
   sszabo@bigpanda.com
   
   On Thu, 26 Oct 2000 pgsql-bugs@postgresql.org wrote:
   
   > Marcel (marcel@simmcomm.ch) reports a bug with a severity of 3
   > The lower the number the more severe it is.
   > 
   > Short Description
   > Select where (upper(xy)~'.CH'); ..matches also SPACE CH
   > 
   > Long Description
   > There exists a problem with the ~ statement. The codesample and the text is from an adult contact database.
   > 
   > The compare string .CH matches the word 'EINEN CHANCE' in the sentence. Seems to be, that the . will match the space between the words. This doesn't happen, if you replace .CH with _CH or something else.
   > 
   > Sample Code
   > meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and (upper(adtext)~'.CH');
   > upper
   > 
   > --------------------------------------------------------------------------------------------------
   > --------------------------------------------------------------------------------------------------
   > ---------------------------------------------------------------------------------
   > WELCHE DOMINA WILL SICH EINEN SKLAVEN HALTEN. BIN EIN 29-JHRIGER]BI-BOY UND SEHR DEVOT. BITTE GEB
   > EN SIE MIR EINEN CHANCE DAMIT ICH IHNEN]BEWEISEN KANN DASS ICH IHR SKLAVE SEIN WERDE UND NUR FR S
   > IE BEREIT BIN]ALLES ZU MACHEN WAS SIE VON MIR VERLANGEN.]UNTERTNIGST IHR SKLAVE]
   > (1 row)
   > 
   > meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and (upper(adtext)~'_CH');
   > 
   > upper
   > -----
   > (0 rows)
   > 
   > meetingpoint=>
   > 
   > No file was uploaded with this report
   >