Oracle Where FirstName and LastName in one column in reverse direction

Markus1980Wien 0
Viewed 15

I have a given database-table where I am not able to change the database-design.

This table contains a column whare names are saved. The Column "UserName" contains values like "Clinton Bill" or "Trump Donald" or "Bush George".

I need a select statement which is able to find the primary key for the "Bill Clinton" or "Donald Trump"-row.

So the order of Firstname and Lastname is reversed. Does anybody know how this can be done.

How the Where-Clouse should look like.

oracle
1 Answers

If you want to "reverse" such a string (that contains of two words), you can use substr + instr or regular expressions, e.g.

SQL> with test (username) as
  2    (select 'Clinton Bill' from dual)
  3  select substr(username, instr(username, ' ') + 1) ||' '||
  4         substr(username, 1, instr(username, ' ') - 1) as reversed_value,
  5         --
  6         regexp_substr(username, '\w+', 1, 2) ||' '||
  7         regexp_substr(username, '\w+', 1, 1) as reversed_value_2
  8  from test;

REVERSED_VALUE       REVERSED_VALUE_2
-------------------- --------------------
Bill Clinton         Bill Clinton

SQL>

Now, just re-use it (whichever option you want) in another query, e.g.

select id
from test
where regexp_substr(username, '\w+', 1, 2) ||' '||
      regexp_substr(username, '\w+', 1, 1) = 'Bill Clinton'
Littlefoot0
Related