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)

Answer 2

I'm not sure how general you need this to be. For your example, you would use the LIKE operator.

WHERE FIELDNAME LIKE 'T_MOBILE'

The wildcard underscore "_" will look for any character matching in that position. The other wildcard you can use is "%" which will match a set of characters of any length.

Answer 3

Function regexp_like should also work here:

SELECT *
FROM "GS Install Base"
WHERE REGEXP_LIKE (company_name, '^T.MOBILE$');

^ - matches beginning of the string

$ - matches end of it

. - matches every char except null