Thread: Like Command returning wrong result set

Like Command returning wrong result set

From
"Tchouante, Merlin"
Date:

Hello group,

 

I’m writing a script and a bit confused on the results with the like command.

 

gm.title like 'CP_%'

 

The above command gives me records with titles like CP_40, CP_2, CP_23, etc.

 

gm.title like '%_CT'

 

The above command give me records with tiles like Quiz: RadiInterpretation w/ CBCT.

 

I was expecting it to return titles that contained *_CT, i.e. FX_CT or DR_CT.  It’s as if it’s ignoring the underscore the way it would do if you were using ‘%CT’.  Does it ignore the underscore when it’s right after the ‘%’?

 

Thanks,

  -- Merlin

 

 

Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu 
410-706-4489 * 410-706-1500 fax

 

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu

Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu

 

New UMB Logo

 

Attachment

Sv: Like Command returning wrong result set

From
Andreas Joseph Krogh
Date:
På torsdag 05. august 2021 kl. 16:00:38, skrev Tchouante, Merlin <mtchouan@umaryland.edu>:

Hello group,

 

I’m writing a script and a bit confused on the results with the like command.

 

gm.title like 'CP_%'

 

The above command gives me records with titles like CP_40, CP_2, CP_23, etc.

 

gm.title like '%_CT'

 

The above command give me records with tiles like Quiz: RadiInterpretation w/ CBCT.

 

I was expecting it to return titles that contained *_CT, i.e. FX_CT or DR_CT.  It’s as if it’s ignoring the underscore the way it would do if you were using ‘%CT’.  Does it ignore the underscore when it’s right after the ‘%’?

 
  • Percent sign ( %) matches any sequence of zero or more characters.
  • Underscore sign ( _)  matches any single character.
 
--
Andreas Joseph Krogh

Re: Like Command returning wrong result set

From
Pavel Stehule
Date:


čt 5. 8. 2021 v 16:04 odesílatel Andreas Joseph Krogh <andreas@visena.com> napsal:
På torsdag 05. august 2021 kl. 16:00:38, skrev Tchouante, Merlin <mtchouan@umaryland.edu>:

Hello group,

 

I’m writing a script and a bit confused on the results with the like command.

 

gm.title like 'CP_%'

 

The above command gives me records with titles like CP_40, CP_2, CP_23, etc.

 

gm.title like '%_CT'

 

The above command give me records with tiles like Quiz: RadiInterpretation w/ CBCT.

 

I was expecting it to return titles that contained *_CT, i.e. FX_CT or DR_CT.  It’s as if it’s ignoring the underscore the way it would do if you were using ‘%CT’.  Does it ignore the underscore when it’s right after the ‘%’?

 
  • Percent sign ( %) matches any sequence of zero or more characters.
  • Underscore sign ( _)  matches any single character.

yes


Regards

Pavel

     
    --
    Andreas Joseph Krogh

    Re: Like Command returning wrong result set

    From
    Christophe Pettus
    Date:
    
    > On Aug 5, 2021, at 07:00, Tchouante, Merlin <mtchouan@umaryland.edu> wrote:
    >
    > Hello group,
    >
    > I’m writing a script and a bit confused on the results with the like command.
    >
    > gm.title like 'CP_%'
    >
    > The above command gives me records with titles like CP_40, CP_2, CP_23, etc.
    >
    > gm.title like '%_CT'
    
    Somewhat confusingly, '_' is the single-character wildcard for SQL's LIKE operation:
    
    xof=# SELECT 'A' LIKE '_';
     ?column?
    ----------
     t
    (1 row)
    
    You can escape it to search for it literally:
    
    xof=# SELECT '_' LIKE '\_';
     ?column?
    ----------
     t
    (1 row)
    
    xof=# SELECT 'A' LIKE '\_';
     ?column?
    ----------
     f
    (1 row)
    
    
    
    
    

    RE: Like Command returning wrong result set

    From
    "Tchouante, Merlin"
    Date:
    This worked, thank you so much; I'm still stuck in Oracle mode.  Thank you to everyone that responded.
    
    Thanks,
      -- Merlin
     
     
    Merlin D. Tchouante, Sr. IT Enterprise Application Developer
    Center for Information Technology Services (CITS) 
    601 West Lombard Street 
    Baltimore, Maryland 21201-1512 
    mtchouan@umaryland.edu  
    410-706-4489 * 410-706-1500 fax
    
    Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu
    Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu
    
    
    
    -----Original Message-----
    From: Christophe Pettus <xof@thebuild.com> 
    Sent: Thursday, August 5, 2021 10:05 AM
    To: Tchouante, Merlin <mtchouan@umaryland.edu>
    Cc: pgsql-sql@postgresql.org
    Subject: Re: Like Command returning wrong result set
    
    CAUTION: This message originated from a non-UMB email system. Hover over any links before clicking and use caution
    openingattachments.
     
    
    > On Aug 5, 2021, at 07:00, Tchouante, Merlin <mtchouan@umaryland.edu> wrote:
    >
    > Hello group,
    >
    > I’m writing a script and a bit confused on the results with the like command.
    >
    > gm.title like 'CP_%'
    >
    > The above command gives me records with titles like CP_40, CP_2, CP_23, etc.
    >
    > gm.title like '%_CT'
    
    Somewhat confusingly, '_' is the single-character wildcard for SQL's LIKE operation:
    
    xof=# SELECT 'A' LIKE '_';
     ?column?
    ----------
     t
    (1 row)
    
    You can escape it to search for it literally:
    
    xof=# SELECT '_' LIKE '\_';
     ?column?
    ----------
     t
    (1 row)
    
    xof=# SELECT 'A' LIKE '\_';
     ?column?
    ----------
     f
    (1 row)