Replace a Space with Special characters in a SELECT

Question

I want to replace the space with Special characters while searching in OBIEE

Example: When I search for "T MOBILE", I find "T-MOBILE" and "T_MOBILE", etc.

Here's my Select statement:

SELECT "- Customer Install At"."Cust Number" saw_0, 
       "- Customer Install At"."Cust Name" saw_1, 
       "- Customer CRI Current Install At"."Global Duns  Number" saw_2, 
       "- Customer CRI Current Install At"."Global Duns Name" saw_3
FROM "GS Install Base"
ORDER BY saw_0, saw_1, saw_2, saw_3

I tried to use REGEXP_LIKE in a WHERE, but it gives me an error "Error getting drill information:"

Can anyone help me with this query?

Answer 1

If you wanted to do something more complex than koriander's answer then the following should work.

You can't use database functions (such regexp_like) directly, only OBI functions. So you need to use the OBI function EVALUATE to pass the regexp_like function back to the database.

(There is plenty of documentation on the EVALUATE function, both by Oracle and others.)

On the column you are trying to filter, you will first need to convert the filter to SQL, replace the entire filter with something like:

evaluate('REGEXP_LIKE(%1, ''^T.MOBILE$'', ''i'')', MyTable.MyColumn)