Perform a particular ILIKE query on Postgresql

I have a problem with a query in Postgres.

I have a column with values such as:

id@@@Name1@@@other text@@@other_text@@@other_text@@@CODE@@@other_text###    
id@@@Name2@@@other text@@@other_text@@@other_text@@@CODE@@@other_text###   
id@@@Name3@@@other text@@@other_text@@@other_text@@@CODE@@@other_text###

Each row can have different string suchs as the above. The @@@ separates each part of the string-block.

The ### separates each string-block.

@@@ and ### are so a “references”.

With my query I have to find, in example:

• The row which block has Name1 and CODE='X-X'

The query:

SELECT * 
FROM table 
WHERE column ILIKE '%**Name1**@@@%@@@%@@@**X-X**%'

doesn’t work. It doesn’t work because the % makes the query take ALL possible parts of text between.

So potentially, it can take (mistakenly) also the table row with Name1 (ok), but where the code X-X is, instead, of the string-block of the Name10 as the example below:

1@@@Name1@@@othertext@@@othertext@@@WRONGCODE@@@othertext### … … 10@@@Name10@@@othertext@@@othertext@@@X-X@@@othertext###

---

SQL provides only 2 wild cards:

• % = take all ,
• _ = jolly only 1 char

But I don’t know how much characters are in the othertext part of the block…

Is there any possible way to perform this with regex? Or make the % work only between 2 fixed parts?

Answer

You could use split_part()

SELECT * 
FROM table 
WHERE split_part(column, '@@@', 2) ILIKE 'Name1' 
  and split_part(column, '@@@', 6) = 'X-X'

split_part() splits the input string based on the delimiter (second parameter) into individual elements. The third parameter identifies the number of the elements (starting at 1, which would be the id in your case)

---

As this gets quite cumbersome if you need to use that as part of other statements, I would create a view, that turns the blocks into columns that are arrays:

create view blocks_and_elements 
as
select ... other columns from the table ...,
       string_to_array(blocks[1], '@@@') as block_1,
       string_to_array(blocks[2], '@@@') as block_2,
       string_to_array(blocks[3], '@@@') as block_3
from (       
  select ... other columns from the table ...,
         string_to_array(data, '###') as blocks
  from the_table
) t1 

This is a bit faster than calling split_part multiple times as the parsing/splitting is only done twice per row, not twice for each item.

Then you can do something like:

select *
from blocks_and_elements
where block_1[2] = 'Name1'
  and block_1[6] = 'X-X'

Or if you need to check multiple items:

select *
from blocks_and_elements
where 'Name1' in (block_1[2], block_2[2])
  and block_1[6] = 'X-X'

---

Another option is to create one array with all elements from the same position:

create view elements
as
select pk, 
       array[block_1[2], block_2[2], block_3[2]] as names,
       array[block_1[6], block_2[6], block_3[6]] as codes
from (       
  select pk, 
         string_to_array(blocks[1], '@@@') as block_1,
         string_to_array(blocks[2], '@@@') as block_2,
         string_to_array(blocks[3], '@@@') as block_3
  from (       
    select pk, 
           string_to_array(data, '###') as blocks
    from the_table
  ) t1 
) t1 

Then you can do queries like:

select *
from elements
where 'Name1'= any(names)
  and 'X-X' = any(codes);

---

And there is also the option to convert the whole delimited string into a JSON value so that you can access the elements by key, rater than position.