How to find substrings in SQL (Postgres)

Question

Let's say I have a column called 'code' in my database table containing string data types.

I want a query that will return all rows with a 'code' value that is a substring of a given search string, with the added condition that the substring appears at the end of the search string.

For example, if the search string is '12345', the query should return all rows with a 'code' value of:
12345
2345
345
45
5

Bohemian · Accepted Answer · 2019-11-18 23:42:47Z

6

Simply use like operator:

SELECT * from yourtable
where '12345' like '%' || Code

edited Nov 18, 2019 at 23:42

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

answered Nov 18, 2019 at 23:31

iljon

4083 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bohemian Over a year ago

Genius! But I would add and code != '' otherwise the blank string will match too (probably not what the OP wants)

Belayer Over a year ago

See Documentation

Gordon Linoff · Accepted Answer · 2019-11-18 23:23:28Z

2

You can use a recursive CTE:

with recursive cte as (
      select code
      from yourtable t
      union all
      select substring(code, 2)
      from cte
      where code > ''
     )
select *
from cte;

Or, you could use a lateral join and generate_series():

select substring(code, i)
from t cross join lateral
     generate_series(1, length(code)) gs(i)

answered Nov 18, 2019 at 23:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to find substrings in SQL (Postgres)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related