CODEWITHSUNDEEP
sqloracle-databaseoracle11g
abhishek shandilya
abhishek shandilya

Reputation: 19

Returning a character from a string SQL

I need to write a SQL query to get all "a" in the string aabcaadfgaaa. I am using Oracle 11g database.

The result for this should be aaaaaaa

So, in case my input is Alaska the result should be aaa.

Any help would be appreciated.

Regards, Abhishek

Upvotes: 1

Views: 56

Answers (2)

JohnHC
JohnHC

Reputation: 11195

Use REGEXP_REPLACE:

SELECT REGEXP_REPLACE ('aabcaadfgaaa', '[^a]') FROM dual;

This is case sensitive, btw.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You have an odd requirement. But you can do this using regexp_replace():

select regexp_replace(lower(col), '[^a]', '', 1, 0)

Upvotes: 6

Related Questions