Condition for oracle sql query

Question

I have a user information search service by its registration in an x table. The registration I receive as parameter for the search comes complete (Ex: T111111), while in the bank it is registered otherwise (Ex: 001111), where can appear only the last 4,5.6 digits of the license plate. Because of this, I’m trying to build a query where I approach all these conditions. So far so good, because I can use substring, but if the user is an intern he comes with the complete registration (E11111), and in this case my substring changes.

Exemplifying:

• Matricula Estagiário = E11111 -> Substr(matriculaFunc, 2)
• Matricula Non Trainee = U111111 -> Substr(matriculaFunc, 4)

Therefore, I am trying to build a query where I check if the user is stable and uses the correct substring for his condition, and if he is not also.

What I’ve gotten so far is this, but it’s not working properly:

SELECT * 
FROM   (SELECT DISTINCT( CASE 
                           WHEN #MATRICULA_FUNC LIKE 'E%' THEN SUBSTR(#MATRICULA_FUNC, 2) 
                           ELSE SUBSTR(#MATRICULA_FUNC, 4) 
                         END ) AS SUBSTR_MAT 
        FROM X) SUBMAT, X
WHERE  MATRICULA LIKE CONCAT('%', SUBMAT.SUBSTR_MAT); 

The idea I had was to create a new table with substring to be able to compare the substr_mat with the enrollment registered in table x. Anyway, it’s not working properly and I wanted to know how I can bring all this user information in this table, upon its registration substring is correctly made.