pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLike Command returning wrong result set
4+ messages / 3 participants
[nested] [flat]
* Like Command returning wrong result set
@ 2021-08-05 14:00 Tchouante, Merlin <mtchouan@umaryland.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Tchouante, Merlin @ 2021-08-05 14:00 UTC (permalink / raw)
To: pgsql-sql
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]
Attachments:
[image/jpeg] image001.jpg (11.5K, ../../CH0PR12MB5105EEAFE0AF3085B4F1095FD2F29@CH0PR12MB5105.namprd12.prod.outlook.com/3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Like Command returning wrong result set
@ 2021-08-05 14:05 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tchouante, Merlin <mtchouan@umaryland.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2021-08-05 14:05 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
č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*
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Like Command returning wrong result set
@ 2021-08-05 14:05 Christophe Pettus <xof@thebuild.com>
parent: Tchouante, Merlin <mtchouan@umaryland.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Christophe Pettus @ 2021-08-05 14:05 UTC (permalink / raw)
To: Tchouante, Merlin <mtchouan@umaryland.edu>; +Cc: pgsql-sql
> 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Like Command returning wrong result set
@ 2021-08-05 14:09 Tchouante, Merlin <mtchouan@umaryland.edu>
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tchouante, Merlin @ 2021-08-05 14:09 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-08-05 14:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-05 14:00 Like Command returning wrong result set Tchouante, Merlin <mtchouan@umaryland.edu>
2021-08-05 14:05 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-08-05 14:05 ` Christophe Pettus <xof@thebuild.com>
2021-08-05 14:09 ` Tchouante, Merlin <mtchouan@umaryland.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox