Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: replace all occurrence of a character

    Hello,

    I want to "delete" (to replace with null) all occurrences of a character . from a string. In fact i have to create a view with phone numbers. For the phone_number field in the database i have some values like this one: 12.34.56.78.90 and I must display it without . character (1234567890).
    It could be done with regular expression. But I'm beginner at this part and i need some help.
    Another solution it's to create a function for that, but i hope that could be do more easily.

    Thanks a lot!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select regexp_replace(phone_number, '[^0-9]', '', 'g')
    from your_table

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    Quote Originally Posted by shammat View Post
    Code:
    select regexp_replace(phone_number, '[^0-9]', '', 'g')
    from your_table
    Great, it works!

    Could you explain me what mean the 3th and 4th parameters, especially 'g'?

    Thanks a lot!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This is all documented in the manual...

    the 3rd parameter is the replacement for the matches found by the regular expression (the 2nd parameter). You can change that to a X if you want to see what it does.

    The 4th parameter tells the function to replace all (g = "global") occurrences that match. Otherwise it would only replace the first occurrence.

  5. #5
    Join Date
    Apr 2012
    Posts
    9
    Quote Originally Posted by shammat View Post
    This is all documented in the manual...

    the 3rd parameter is the replacement for the matches found by the regular expression (the 2nd parameter). You can change that to a X if you want to see what it does.

    The 4th parameter tells the function to replace all (g = "global") occurrences that match. Otherwise it would only replace the first occurrence.
    thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •