Putting special character in middle of string.

Recently in OTN someone posted below SQL requirement.

“In one of my column having value of string like ‘12345678’.
Now i want not all of these value would be visible. So i would put * on some characters. After updating the value would be like:
‘123***789’. Can any one advise how can i do the update????”

Below was my solution.

With Data As
(SELECT '123456789' value FROM dual
Data1 AS
(SELECT value,LENGTH(value) col1 FROM data
-- getting total lenght of character!
Data2 AS
(Select Value,Decode(Mod(Col1,2),0,Col1/2,Trunc((Col1/2)+1)) Col2 From Data1
/* finding mid of character input as per odd or even length
USING SUBSTR AND REPLACE function alter middle of 3 CHARACTER to "***" */
|| REPLACE(SUBSTR(Value,Col2 - 1,3),SUBSTR(Value,Col2 - 1,3),'***')
|| Substr(Value,Col2 +2,Length(Value))
From Data2;
OR Replace 5 Character *****
Using Substr and replace we will replace by 5 character !
Select Value,Substr(Value,0,Col2-4) || Replace(Substr(Value,Col2 - 1,4),Substr(Value,Col2 - 1,4),'*****') ||
SUBSTR(VALUE,col2+4,length(value)) from Data2;*/

Output : 123***789

OTN URL For reference and alternate solutions.


About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s