CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
user15906795
user15906795

Reputation: 39

Extract integer from a string in Oracle SQL and find and replace a specific character with another

I am trying to extract numbers from a string comprises of alpha and special characters like Green - 100% Green - 90% Red - 0% Red - 25% Yellow - 50% Yellow - 75% I tried REGEXP_REPLACE (COLUMN_NAME, '[[:alpha:]]') but this will leave - and %. Should i add two more REGEXP to replace the - and %? Or is there an effective way to do it in one function?

Also in another problem i need to identify the double quotes in a string and replace with two times of double quotes and finally wrap entire string in double quotes eg: Example" should be converted to "Example""" Sam"ple should be converted to "Sam""ple"

Appreciate your solution

Upvotes: 1

Views: 409

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

To extract only number part please use '[^[:digit:]]' instead of '[[:alpha:]]' with regexp_replace()

regexp_replace(column_name, '[^[:digit:]]', '')

You can use below query to resolve your second problem.

select '"'||replace('Example"','"','""')||'"' from yourtable

Upvotes: 1

Related Questions