0

I have a table like this :

> create table sample(id int, identity varchar(20)) ;
> insert into sample (id,identity) values (1,'9822');
> insert into sample (id,identity) values (2,'129822');
> insert into sample (id,identity) values (3,'ABCD9822');
> insert into sample (id,identity) values (4,'1234');

------------------------
> select * from sample ;
 id | identity 
----+----------
  1 | 9822
  2 | 129822
  3 | ABCD9822
  4 | 1234
(4 rows)

I have a string like 0987129822 .
how to select on database to fetch all identities ends matches by this string ?
I need this result :

 id | identity 
----+----------
  1 | 9822
  2 | 129822

Note : maybe this question duplicated , but i can not understand what keyword should i search on google .

5
  • ABCD9822 should NOT match? Commented Nov 9, 2023 at 21:09
  • @FrankHeikens , oh sorry , no , could not match Commented Nov 9, 2023 at 21:12
  • Does this answer your question? Using column data as pattern for regexp match Commented Nov 9, 2023 at 21:29
  • 1
    you can also swap the pattern using like: select * from sample where '0987129822' like '%' || identity; Commented Nov 9, 2023 at 21:34
  • Is any solution ok, or is this about performance optimization? Is there a minimum length / minimum match length? Is the search pattern always at least as long as the longest matching identity or can the identity have additional leading characters? Like XXX0987129822? Your Postgres version? Commented Nov 9, 2023 at 23:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.