[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2873
  • Last Modified:

How do I replace numbers to asterisk in SQL?

How do I replace numbers to asterisk in SQL?
0
d2marcelo
Asked:
d2marcelo
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify that with examples, please? not 100% sure what you want to get...
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Or maybe the REPLACE function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace, like this:
---
UPDATE yourTable SET yourField = REPLACE(yourField, "1", "*");
---
0
 
sujith80Commented:
Is this what you are looking for?

SQL> select * from tbl2;

VAL
--------------------
hello
test111one
123
444test

SQL> select val, translate(val, 'a0123456789','a**********') from tbl2;

VAL                  TRANSLATE(VAL,'A0123456789','A**********')
-------------------- ---------------------------------------------------
hello                hello
test111one           test***one
123                  ***
444test              ***test
0
 
awking00Commented:
Unfortunately, I don't think MySql supports the translate function, so I think you have to use nested replace functions -
select replace
      (replace
      (replace
      (replace
      (replace
      (replace
      (replace
      (replace
      (replace
      (replace(yourfield,'0','*')
                        ,'1','*')
                        ,'2','*')
                        ,'3','*')
                        ,'4','*')
                        ,'5','*')
                        ,'6','*')
                        ,'7','*')
                        ,'8','*')
                        ,'9','*')
from yourtable;
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now