CODEWITHSUNDEEP
sqloracle-database
Gunnu
Gunnu

Reputation: 13

Separating special characters and display them

I have a table with a column Name, like this:

Name
ftyrt_hghjh
hgdhfg_jhjh
dfgfh#hvhvj
vhhghf=dvdf
hdghfg%bdjfb

I want to separate the special characters and display them like below:

Name             Special_Char
ftyrt_hghjh          _    
hgdhfg_jhjh          _
dfgfh#hvhvj          #
vhhghf=dvdf          =
hdghfg%bdjfb         %

Upvotes: 0

Views: 125

Answers (2)

saikumarm
saikumarm

Reputation: 1575

with temp as 
(select 'ftyrt_hghjh' as name from dual
 union all
select 'hgdhfg_jhjh' as name from dual
 union all
select 'dfgfh#hvhvj' as name from dual
 union all
select 'vhhghf=dvdf' as name from dual
 union all
select 'hdghfg%bdjfb' as name from dual
)
select name AS "Name"
      ,regexp_substr(name, '[^a-zA-Z]') AS "Special Character"
from temp

match any character other than a-z or A-Z with regexp Match Special Character

Upvotes: 1

D. Mika
D. Mika

Reputation: 2808

You can use the function translate to replace all non-special characters with nothing, actually removing them from the input string.

Assuming the special characters are

_ # = %

and assuming the non-special characters are all lowercase from a to z the following will work:

translate (table.name, '_#=%abcdefghijklmnopqrstuvwxyz', '_#=%')

Upvotes: 0

Related Questions