SQL Question about like

On 8/10/20 11:37 AM, pgml@gmx.de wrote:

Hello,

my SQL question is, why psql doesn't return the record?

create table lll (text char(100));
insert into lll (text) values ('10% - Ersthelfer');

select * from lll where text like '10% - Ersthelfer';

Other databases (Maria, SQL Server, YARD) do this.
What can I do in pg, to get the result?

Add a wildcard character.  (Also, why use LIKE in an equality?)

test=# select * from lll where text like '10% - Ersthelfer';
 text
------
(0 rows)

test=# select * from lll where text like '10% - Ersthelfer%';
text
--------------------------------------------------------------
 10% - Ersthelfer
(1 row)

test=#
test=#
test=# select * from lll where text = '10% - Ersthelfer';
text
--------------------------------------------------------------
 10% - Ersthelfer
(1 row)

--
Angular momentum makes the world go 'round.

On 8/10/20 9:37 AM, pgml@gmx.de wrote:

Hello,

my SQL question is, why psql doesn't return the record?

create table lll (text char(100));
insert into lll (text) values ('10% - Ersthelfer');

select * from lll where text like '10% - Ersthelfer';

Other databases (Maria, SQL Server, YARD) do this.
What can I do in pg, to get the result?

A little documentation goes a long way:) :

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE

Thank you and best regards, Martin

--
Adrian Klaver
adrian.klaver@aklaver.com

The problem is your field is fixed length text, change it to varchar(100)
or text and it works without the wildcard at the end.
--
Mike Nolan

On 8/10/20 10:01 AM, Michael Nolan wrote:

The problem is your field is fixed length text, change it to
varchar(100) or text and it works without the wildcard at the end.

That assumes values don't get entered with spaces:

create table lll (text varchar(100));
insert into lll (text) values ('10% - Ersthelfer');
insert into lll (text) values ('10% - Ersthelfer ');

select * from lll where text like '10% - Ersthelfer';
text
------------------
10% - Ersthelfer
(1 row)

select * from lll where text like '10% - Ersthelfer%';
text
---------------------
10% - Ersthelfer
10% - Ersthelfer

I have seen that enough times to default to using wildcard if I am
really looking for LIKE not =.

--
Mike Nolan

--
Adrian Klaver
adrian.klaver@aklaver.com

I usually use something like trim(field) like 'pattern'. Eliminates the
need for the wildcard at the end. I find I use the ~ operator more than
'like' though.
--
Mike Nolan

On Mon, Aug 10, 2020 at 12:24 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Sorry about the top-posting, Firefox and I disagreed about whether I was
done editing the previous message.

--
Mike Nolan