Horton

Thread

  1. Like Command returning wrong result set

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2021-08-05T14:00:38Z

    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<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]

  2. Sv: Like Command returning wrong result set

    Andreas Joseph Krogh <andreas@visena.com> — 2021-08-05T14:04:24Z

    På torsdag 05. august 2021 kl. 16:00:38, skrev Tchouante, Merlin <
mtchouan@umaryland.edu <mailto: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

  3. Re: Like Command returning wrong result set

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-08-05T14:05:07Z

    č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

https://www.postgresql.org/docs/current/functions-matching.html

Regards

Pavel


>
>
> --
> *Andreas Joseph Krogh*
>

  4. Re: Like Command returning wrong result set

    Christophe Pettus <xof@thebuild.com> — 2021-08-05T14:05:13Z

    
> 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)

  5. RE: Like Command returning wrong result set

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2021-08-05T14:09:41Z

    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 opening attachments.

> 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)