Search code examples
sqloracle-databasesql-like

PLSQL search string using LIKE backwards

I have a view which has VARCHAR2 column. The column contains a string which is concatenation of 2 columns from a different table.

For example I search "James Smith". I search the column using LIKE:

LOWER(USER_LIST.SEARCH_STRING) LIKE LOWER 'James Smith'

I get the results just fine. I would like to know if there's an option to perform a reverse search (still using LIKE) and getting the same results, like so:

    LOWER(USER_LIST.SEARCH_STRING) LIKE LOWER 'Smith James'

Please note that I'm aware that using regex or adding an additional column to the view can resolve this, but I wish to make as minimal changes as possible.

Thanks in advance.

Solution

  • I hope the below answer illustrates your requirement. 

    SELECT A.NM
FROM
  (SELECT 'Avrajit Roy' nm FROM dual
  )A
WHERE lower(A.NM) LIKE lower('avrajit roy')
OR TRIM(lower(SUBSTR(a.nm,instr(a.nm,' ',1)+1,LENGTH(a.nm))
  ||' '
  ||SUBSTR(a.nm,1,instr(a.nm,' ',1)))) LIKE lower('roy avrajit');