How to compare trimmed variable in SQL statement?

I have a SQL statement which includes three database tables. I am comparing some IDs and DESCRIPTIONs and now I need to compare results FIRSTNAME, MIDDLENAME and SURNAME.

SELECT UGOVARATELJ_OSIGURANJA AS ULJEZ FROM INS_RAZNO.TEST_DOWNJONES 
WHERE UGOVARATELJ_OSIGURANJA IN 
(SELECT  FIRSTNAME||' '||MIDDLENAME||' '||SURNAME 
AS IME_PREZIME 
FROM (SELECT FIRSTNAME, MIDDLENAME, SURNAME 
FROM INS_RAZNO.AML_CTF
WHERE ID IN (SELECT ID FROM INS_RAZNO.AML_CTF_DESCRIPTIONS 
WHERE DESCRIPTION1='1' OR DESCRIPTION1='2')));

Variable UGOVARATELJ_OSIGURANJA is actually name of the client in form NAME MIDDNAME SURNAME, with spaces between each part of the name. My idea was to remove those spaces with REMOVE() but when I compare after that with second table there is no correct result.

I tried to test my statement with this: THIS IS ONLY TEST STATEMENT

SELECT REPLACE(UGOVARATELJ_OSIGURANJA, ' ','') AS ULJEZ FROM 
INS_RAZNO.TEST_DOWNJONES WHERE UGOVARATELJ_OSIGURANJA LIKE '%IVAN F%'

Notice that I had to set space between name and middname LIKE %IVAN F% and correct way would be %IVANF%.

Any idea how to solve this? Thanks in advance

EDIT: I am doing this replacing (trimming) because some of the peops on my lists have not MIDDLENAME

EDIT: This is working statement for names which has MIDDLENAME. The statement is not working properly when someone has no MIDDLENAME because of one space too much.

Answer

This is working statement for names which has MIDDLENAME. The statement is not working properly when someone has no MIDDLENAME because of one space too much.

You can make the extra space conditional:

SELECT UGOVARATELJ_OSIGURANJA AS ULJEZ FROM INS_RAZNO.TEST_DOWNJONES 
WHERE UGOVARATELJ_OSIGURANJA IN 
(SELECT  FIRSTNAME||
   -- only add a space before middlename if it has a value
   CASE WHEN MIDDLENAME IS NOT NULL THEN ' ' END
   ||MIDDLENAME||' '||SURNAME 
AS IME_PREZIME 
FROM (SELECT FIRSTNAME, MIDDLENAME, SURNAME 
FROM INS_RAZNO.AML_CTF
WHERE ID IN (SELECT ID FROM INS_RAZNO.AML_CTF_DESCRIPTIONS 
WHERE DESCRIPTION1='1' OR DESCRIPTION1='2')));

This will remove the potential for false matches from removing the spaces.

You could simplify this slightly by removing one unnecessary level of subquery:

SELECT UGOVARATELJ_OSIGURANJA AS ULJEZ
FROM INS_RAZNO.TEST_DOWNJONES 
WHERE UGOVARATELJ_OSIGURANJA IN (
  SELECT  FIRSTNAME||
    CASE WHEN MIDDLENAME IS NOT NULL THEN ' ' END||MIDDLENAME
    ||' '||SURNAME
  FROM INS_RAZNO.AML_CTF
  WHERE ID IN (
    SELECT ID
    FROM INS_RAZNO.AML_CTF_DESCRIPTIONS 
    WHERE DESCRIPTION1='1' OR DESCRIPTION1='2'
  )
);

or change it to use joins instead – I think this is equivalent:

SELECT TD.UGOVARATELJ_OSIGURANJA AS ULJEZ
FROM INS_RAZNO.AML_CTF_DESCRIPTIONS ACD
JOIN INS_RAZNO.AML_CTF AC ON AC.ID = ACD.ID
JOIN INS_RAZNO.TEST_DOWNJONES TD
ON TD.UGOVARATELJ_OSIGURANJ = AC.FIRSTNAME
  ||CASE WHEN AC.MIDDLENAME IS NOT NULL THEN ' ' END||AC.MIDDLENAME
  ||' '||AC.SURNAME
WHERE ACD.DESCRIPTION1 IN ('1', '2');

Also check the data type of DESCRIPTION1 – if that is actually a number then you should use ACD.DESCRIPTION1 IN (1, 2) rather than forcing an unnecessary conversion between number and string.