How to Identify and Replace the Last word in an address string ?

Rohit Trehan

Hi Users

I have this unique String Manipulation issues at hand.

Let me first paste the picture as pictures speak better than words:

I have 2 issues at hand;

1. Identify the LAST WORD in a given string - Done. "RD"

2. Replace that word if it is "RD" then change it to "ROAD"; If it is "AVE" then change it to "AVENUE"

I am looking for some guidance on the OBIEE command for that string manipulation.

Thanks

Rohit

	Start String	Identify the Last Word	Replace the Last word if it   is "RD"
Input String	45 QUEEN EDWARD RD	RD	45 QUEEN EDWARD ROAD
OBIEE Command		RIGHT(REPLACE("Start String", ' ', REPEAT(' ', 100)),   100)	?????

Gianni Ceresa

Hi,

Data quality would be better in the DB during ETL directly ...

You have the last word (maybe need to trim it .... just in case ), take the first part of the string (as you have the last word you can do a substring or left based on the length of the string minus the length of the last word), then you concatenate a CASE WHEN on the last word.

It will be ugly as you keep repeating the formulas in various places, the query sent to your DB will be as ugly as your formula ... but that's the price to pay

[Deleted User]

+1'000'000 to Gianni

Doing that in OBIEE - in the very last step of the food chain - is the most inefficient way conceivable to do this. It will have to execute the logic for every single row parsed in every single query fired off against that data source.

Rohit Trehan

In an ideal world everything should be pushed to ETL, but in a practical world you have to deal with it based on case by case.

Rohit Trehan

Let me try this Gianni !

Rohit Trehan

Thanks.

I was able to crack it. Here is the code:

SUBSTRING("Input String"  FROM 1 FOR length("Input String") - LENGTH(TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100))) ) || case when TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100)) = 'RD' then 'ROAD'

when TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100)) = 'AVE' then 'AVENUE'

else TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100))  end

Gianni Ceresa

To avoid repeating the piece of code 2 times change the CASE WHEN <condition> to a CASE <column> WHEN <value> :

SUBSTRING("Input String"  FROM 1 FOR length("Input String") - LENGTH(TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100))) ) || case TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100)) WHEN 'RD' then 'ROAD'

when 'AVE' then 'AVENUE'

else TRIM(RIGHT(REPLACE("Input String", ' ', REPEAT(' ', 100)), 100))  end

So if you need to add new values it's easier (as long as the condition is '=').