How to avoid use of concat

Question

I got a piece of SQL code I need to rewrite to a version that does not use the concat function.

SELECT *
FROM A
WHERE concat(name_1, name_2) in (select concat(name_1, name_2) from B)

Is this possible?

Answer 1

Check that substrings of equal lengths are the same:

Oracle SQL

(Since you haven't tagged the question with the SQL dialect you are using, however you can adapt the answer to any SQL dialect.)

So, if your sample data is:

CREATE TABLE a ( name_1, name_2 ) AS
SELECT 'ABC', 'DEF' FROM DUAL UNION ALL
SELECT 'GHI', 'JKL' FROM DUAL UNION ALL
SELECT 'MNO', 'PQR' FROM DUAL;

CREATE TABLE b ( name_1, name_2 ) AS
SELECT 'ABC', 'DEF' FROM DUAL UNION ALL
SELECT 'GH', 'IJKL' FROM DUAL UNION ALL -- same letters, left-hand side is shorter
SELECT 'MNOP', 'QR' FROM DUAL;          -- same letters, left-hand side is longer

Then you can use the query:

SELECT *
FROM   A
WHERE  EXISTS(
  SELECT 1
  FROM   B
  WHERE  LENGTH( A.name_1 ) + LENGTH( a.name_2 )
          = LENGTH( b.name_1 ) + LENGTH( b.name_2 )
  AND    (  ( A.name_1 = B.name_1 AND A.name_2 = B.name_2 )
         OR (   LENGTH( A.name_1 ) < LENGTH( B.name_1 )
            AND A.name_1 = SUBSTR( b.name_1, 1, LENGTH( a.name_1 ) )
            AND SUBSTR( A.name_2, 1, LENGTH( b.name_1 ) - LENGTH ( a.name_1 ) )
                = SUBSTR( b.name_1, LENGTH( a.name_1 ) + 1 )
            AND SUBSTR( a.name_2, LENGTH( a.name_2 ) - LENGTH( b.name_2 ) + 1 )
                = B.name_2
            )
         OR (   LENGTH( A.name_1 ) > LENGTH( B.name_1 )
            AND b.name_1 = SUBSTR( a.name_1, 1, LENGTH( b.name_1 ) )
            AND SUBSTR( b.name_2, 1, LENGTH( a.name_1 ) - LENGTH ( b.name_1 ) )
                = SUBSTR( a.name_1, LENGTH( b.name_1 ) + 1 )
            AND SUBSTR( b.name_2, LENGTH( b.name_2 ) - LENGTH( a.name_2 ) + 1 )
                = a.name_2
            )
         )
)

Which outputs:

NAME_1 | NAME_2
:----- | :-----
ABC    | DEF   
GHI    | JKL   
MNO    | PQR   

The same output as for your query using CONCAT.

db<>fiddle here

---

Or you may be able to use the || concatenation operator:

SELECT *
FROM A
WHERE name_1 || name_2 in (select name_1 || name_2 from B)

db<>fiddle here