0

I want to replace parts information in my strings.

e.g. From +1-2024561111 To +1-202***1111

I found a query REPLICATE which could achieve what I want. However, it seems not available in sqlite3.

And the REPLACE in sqlite can only replace some certain strings. What I need is to replace strings in certain positions. One alphabet replaces one '*' ; they need to have the same length after replacing.

Even the Excel can do this easily by using REPLACE(old_text, start_num, num_chars, new_text). Is there any way to solve this? BTW, I'm using sqlite3 in python. The solution written in python is also acceptable.

WenT
  • 157
  • 8
  • So different substrings are to be handled differently? Why can't you use `substr()`? – CL. Jan 25 '16 at 22:10

2 Answers2

2
REPLACE(old_text, start_num, num_chars, new_text)

should be identical to

SUBSTR(old_str, 1, start_num - 1) || new_text || SUBSTR(old_str, start_num + LENGTH(new_text))
Hugh Bothwell
  • 55,315
  • 8
  • 84
  • 99
1

Using substr and replace in sqlite

I finally use substr(x,y,z) to get the first z chars from y(I set 1 to get start at the beginning of the original string).

Then use || to connect two strings. It's a little different with the answer provided by @Hugh Bothwell.

After ||, I use replace(substr(quote(zeroblob((Y + 1) / 2)), 3, Y), '0', '*') to fill the string to length Y. It's a little hard to understand at the beginning.This website has more information for you.

I need my string to be masked after 5th chars, so I don't need to append anything behind the replace statement. If you need, just add another || and use substr().

In short, what I did is: +1-2024561111 (original string)

  • substr() ➜ +1-202456
  • || ➜ to append another string
  • the replace statement ➜ to fill string to a certain length ➜ +1-202456****


Another solution: in python

It's a normal statement to print database in sqlite3 in python like this:

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

I add a if-else statement and use ljust to do what I want.

It's a sample below:

-- row[0]: the first filed in table, here means numbers

    if len(row[0])>=9:
            newNum=row[0][0:10].ljust(len(row[0]),'*')
                        #^^^^^^ to get first 9 chars in row[0]

    # ljust fill in '*' from 10th char to the length of row[0]

    print(newNum) # use newNum instead of row[0]
WenT
  • 157
  • 8