Forum Stats

  • 3,734,283 Users
  • 2,246,938 Discussions


Display Code (+91 123456789 to +91 xxxxx6789 in a table

User_X9S6M Member Posts: 38 Red Ribbon
edited Feb 3, 2021 8:36AM in SQL & PL/SQL

Input : +91 123456789

Expected result : +91 xxxxx6789


  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    Not familiar with the term "happy code". So:

    First step: Google search for "happy code". Didn't find anything meaningful.

    Second step: Try to guess based on the example. So: The lower-case u from the input becomes upper-case U in the output. Then the next six characters are replaced with seven asterisks. Finally, the last character is copied as-is. (But, is that only because it's a digit? What if, for example, it was a lower-case letter... should it be upper-case in the output?)

    Too much of a mystery for me. Pass.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi, @User_X9S6M

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. You don't need to post a lot of sample data; exactly how much depends on your requirements, but one row is not enough. Post sample data that includes all special cases you need to handle, such as

    • strings of different lengths
    • very short strings
    • NULL
    • no space characters

    Always say which version of Oracle you're using (e.g.

    See: How to Ask Questions in Developer Community Spaces - oracle-tech

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond


    Here's an example of how you should post your requirements: "I want to obfuscate a VARCHAR2 column called str. I want to returns a string of the same length as str, with the same first 4 characters and the same last 4 characters, but all 'x's in between. It doesn't matter what any of the original characters are, digits, letters, spaces and other symbols. I have a CHECK constraint which guarantees that str won't be shorter than 10 characters long. If it is NULL, I want to return NULL."

    If those happen to be your requirements, you could do something like this:

    SELECT SUBSTR (str, 1, 4)
        || LPAD ('x', LENGTH (str) - 8, 'x')
        || SUBSTR (str, -4) AS new_str
    FROM   table_x

Sign In or Register to comment.