CODEWITHSUNDEEP
sqloracle-databasereplaceoracle12c
sonam
sonam

Reputation: 1

Issue with replace function in Oracle

I want to replace 6 of the last 10 digits in a string with XXXXXX. The length of the string can be 16 or 19.

Using below query:

SELECT REPLACE('0000000000000000000',SUBSTR('0000000000000000000',-10,6), 'XXXXXX') FROM DUAL;
--Actual Output  --XXXXXXXXXXXXXXXXXX0
--Expected Output--000000000XXXXXX0000

SELECT REPLACE('1234561234561234561',SUBSTR('1234561234561234561',-10,6), 'XXXXXX') FROM DUAL;
--Actual Output  --123XXXXXXXXXXXX4561
--Expected Output--123456123XXXXXX4561

SELECT REPLACE('0004421640006525212',SUBSTR('0004421640006525212',-10,6), 'XXXXXX') FROM DUAL;
--Actual Output  --000442164XXXXXX5212
--Expected Output--000442164XXXXXX5212

Why do the first two give the wrong result, and how can I fix the query?

Upvotes: 0

Views: 184

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

If the length of the string was always 19 you could do:

substr('0004421640006525212', 1, 9) || 'XXXXXX' || substr('0004421640006525212', -4)

With two possible lengths you could use a case expression to decide the second argument for the first substr() call, based on the actual string length; or you could allow for any length (of at least 10, anyway) with:

substr('0004421640006525212', 1, length('0004421640006525212') - 10) || 'XXXXXX' || substr('0004421640006525212', -4)

or with a placeholder/column for brevity:

substr(str, 1, length(str) - 10) || 'XXXXXX' || substr(str, -4)

Or maybe simpler, but slower, you could use a regular expression:

regexp_replace('0004421640006525212', '^(.*?)(.{6})(.{4})$', '\1XXXXXX\3')

The regular expression splits the string into three groups; working backwards, (.{4})$ is a group of exactly four characters at the end of the string; then (.{6}) is a group of exactly six characters (the ones you want to replace); then ^(.*} is a group of any/all the remaining characters from the start of the string. The replacement pattern keeps the first and third groups - with \1 and \3 - and puts the fixed Xs between those. The second group - of six characters - is discarded.

SQL Fiddle getting the values, and a couple of shorter ones, form a table to avoid having to repeat them all; which also shows the first version doesn't work properly with varying lengths.

The replace function replaces every occurrence of one string with another. It doesn't know or care how the second argument is generated; it doesn't know you're getting it from a particular position in the same string.

When you do:

REPLACE('0004421640006525212',SUBSTR('0004421640006525212',-10,6), 'XXXXXX')

the SUBSTR() evaluates to '000652', so it's effectively:

REPLACE('0004421640006525212','000652', 'XXXXXX')

and that does what you want, because that substring only appears once in the original string. But with:

REPLACE('1234561234561234561',SUBSTR('1234561234561234561',-10,6), 'XXXXXX')

the SUBSTR() evaluates to '456123', so it's effectively:

REPLACE('1234561234561234561','456123', 'XXXXXX')

and that appears multiple times in the original string:

1234561234561234561
   ^^^^^^
         ^^^^^^

and both of those are replaced. With all zeros it's even worse; the SUBSTR() is now '000000', so it matches three times:

0000000000000000000
^^^^^^
      ^^^^^^
            ^^^^^^

and all three of those are replaced.

Upvotes: 2

Related Questions