Oracle SQL replace some string in a string FAST

Question

I have a really simple problem: Replace XX_FistName LastName (maybe dr. or phd anything trash) to firstname lastname

now i am using regexp replace:

trim(regexp_replace(lower(vhc.name),'xx_|dr|\.|\,|phd|jr','')

but this is really really slow... can you give me some hint how can i replace this something faster? I have too much data to compare and do other functions on these data and it takes hours.

If you are comparing the expression, you need a function-based index. If you can tell us what exactly is the query and some sample data, someone can provide you a specific answer. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 18, 2014 at 13:26
@Csanesz regexp have the reputation of being slow. Mostly because they require to be somehow compiled before processing. But once done, they are not that slow at all (esp. without loops and lookahead). Could you show your actual query? How many rows do you have? Any index on the relevant rows? — Sylvain Leroux
– Sylvain Leroux, Commented Oct 18, 2014 at 16:33

Avinash Raj · Accepted Answer · 2014-10-18 08:49:44Z

1

A reduced one.

trim(regexp_replace(lower(vhc.name),'xx_|[dj]r|[.,]|phd','')

answered Oct 18, 2014 at 8:49

Avinash Raj

175k32 gold badges247 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SüniÚr Over a year ago

thx but i actually want to drop this regexp replace and something else if its exists.

Maheswaran Ravisankar Over a year ago

@Csanesz the slowness could be with your query. regexp_replace is bit CPU intensive but not that slow. Can you share your query

khalidmehmoodawan · Accepted Answer · 2021-11-29 10:45:52Z

0

Late response, but might help someone landing here just like me.

replace(table1.column_here, 'string_to_be_replaced', 'to_replace_with')

answered Nov 29, 2021 at 10:45

khalidmehmoodawan

6791 gold badge8 silver badges22 bronze badges

Collectives™ on Stack Overflow

Oracle SQL replace some string in a string FAST

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related