How to replace digits with "!" in SQL (postgresql)

Asked

Viewed 57 times

-1

Good Afternoon,

I am trying in an output to exchange all digits in the address column for a '!'. I tried the following solution among others, but for some reason I’m not getting what I want.

select project, 
   commits, 
   contributors, 
   replace(address, '[0-9]', '!') as address
from repositories

Output: what I have - 1ECnGua88LpbYq5ta88ZwV8qmnoYj3Mibo --> what I want ! Ecngua!! Lpbyq! ta!! Zwv! qmnoYj! Mibo

Thanks for the help

sql postgresql replace

  • Which database you are using?

  • I’m using the postgresql

  • 1

    SELECT REGEXP_REPLACE(address,'[[:Digit:]]','!','g'); https://www.postgresqltutorial.com/regexp_replace/

  • The best option is certainly the function regexp_replace, as answered by Leo. The function replace function character by character: SELECT replace(replace(replace(replace(replace('1ECnGua88LpbYq5ta88ZwV8qmnoYj3Mibo', '1', '!'), '3', '!'), '4', '!'), '5', '!'), '8', '!'); and the function translate with the corresponding position: SELECT translate('1ECnGua88LpbYq5ta88ZwV8qmnoYj3Mibo', '0123456789', '!!!!!!!!!!');.

1 answer

2


REGEXP_REPLACE() - Postgresql function - replaces substrings that correspond to a regular expression with a new substring.

    REGEXP_REPLACE(source, pattern, replacement_string,[, flags])

  1. source - is a string in which the substitution should be performed.

  2. Pattern - a regular expression with which the element value is checked

  3. replacement_string - replacement string

  4. flags - flags that affect search g search for all matches.

    SELECT REGEXP_REPLACE(address,'[[:digit:]]','!','g');

Browser other questions tagged sql postgresql replace

You are not signed in. Login or sign up in order to post.