How to match length of field in like search in postgresql?

Question

Hi I have a following query where conclusion_number column may include expertize_number with additional text. Thus I'm trying to find conclusion_number inside expertize_number via LIKE operator. However problem I'm facing is records starting with lesser or more characters in conclusion_number are also included, but I need to find only records where they match exactly the same. Is it possible to get length of a.conclusion_number used in like operator and compare it to length of rg.expertize_number? Thank you.

SELECT rg.task_id, a.id, a.conclusion_number, a.expertize_number, rg.id
From registration rg
         JOIN audit a on rg.expertize_number like '%' || a.conclusion_number || '%'
WHERE rg.task_id = '23402928';

Here as you can see many matches the only one I need is, like it's emphasized where conclusion_number matches expertize number both in value and length. Like in image conclusion_number is 132736 and length is 6 so it should match substring in expertize number and return this record.

Column data types are varchar (except for ids) as it may include alphanumeric values.

Answer 1

... but I need to find only records where they match exactly the same.

So simply use the equality operator =:

SELECT rg.task_id, a.id, rg.expertize_number, rg.id
FROM   registration rg
JOIN   audit a ON rg.expertize_number = a.conclusion_number
WHERE  rg.task_id = 23402928;

If you are bound to the LIKE operator (construct, really) for some odd reason, use a pattern without wildcards:

JOIN   audit a ON rg.expertize_number LIKE a.conclusion_number

Make sure the pattern does not include wildcards. See:

• Escape function for regular expression or LIKE patterns