0

There's a need for a query to search on all table columns using one single row. This is the query I have so far:

select * from my_table 
where
instr( 
Col01 || Col02 || Col03 || Col04 || Col05 || Col06 || Col07 || Col08 || Col09, 'My Searched 
Text') > 0

But as per the requirements, the full table must be displayed when the searched value is null. Is there any way to achieve this?

Filburt
  • 17,626
  • 12
  • 64
  • 115
JB999
  • 441
  • 3
  • 16
  • 1
    Can you update your post with sample data and expected output? – lemon May 26 '23 at 20:49
  • 1
    ...why aren't you doing it properly by using `INSTR` on each column separately? Using this approach your query won't be SARGable and will have horrible runtime performance. – Dai May 26 '23 at 20:49
  • 1
    Are you looking for `where instr(Col01 || Col02 || ..., :search) > 0 or :search is null`? – Thorsten Kettner May 26 '23 at 21:12
  • 2
    @Dai: This will be a full table scan anyway, so there shouldn't be much difference in runtime. But it makes a difference semantically whether to concatenate the strings or not. E.g. 'Christmas' won't be found in 'Jesus Christ' and 'master class', but will be found in the concatenated 'Jesus Christmaster class' :-) – Thorsten Kettner May 26 '23 at 21:17

1 Answers1

-1

You could do it like this:

NOTE: Just because this is how you would update your bad solution does not make this a good idea. You should do a instr on each column and use OR between them.

instr(
  coalesce(Col01,'')  || 
  coalesce(Col02,'')  ||
  coalesce(Col03,'')  || 
  coalesce(Col04,'')  || 
  coalesce(Col05,'')  || 
  coalesce(Col06,'')  || 
  coalesce(Col07,'')  ||
  coalesce(Col08,'')  ||
  coalesce(Col09,'') , 'My Searched Text') > 0

Another option is to create a "calculated" column that is auto generated on table insert or change that contains the above concatenation. Then you only need to search one column at query time.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • 2
    In Oracle the empty string '' is considered null. `coalesce(Col0x,'')` is pointless hence. – Thorsten Kettner May 26 '23 at 21:19
  • @ThorstenKettner huh? – Hogan May 26 '23 at 21:21
  • 3
    Yep. In Oracle, `coalesce(Col0x,'')` is the same as `coalesce(Col0x,null)`, which is the same as `Col0x` obviously. – Thorsten Kettner May 26 '23 at 21:23
  • @ThorstenKettner -- I ran some tests -- my code works as expected on 3 different platforms. I'm not sure of your point given this code works. – Hogan May 26 '23 at 21:24
  • 2
    Yes it works. Same as `Col01 || Col02 || Col03 ...`. Because in Oracle, `'A' || null || 'B'` is `'AB'`. Demo: https://dbfiddle.uk/2JlV1QqZ – Thorsten Kettner May 26 '23 at 21:26
  • Thank you all, followed the suggestions and it worked: (by column) instr(lower(Col01), nvl(lower('searched text'), lower(Col01))) > 0 or instr(lower(Col02), nvl(lower('searched text'), lower(Col02))) > 0 – JB999 May 26 '23 at 21:52