CODEWITHSUNDEEP
postgresqlfunctionvarchar
sc-leeds
sc-leeds

Reputation: 585

postgres function to remove numbers from data in columns

I've found a stackoverflow answer on how to create a function in MSSQL to remove numerical values from a varchar column, I need to translate this into Postgres if possible. This could do with being a function as I need to use it multiple times across a few different databases.

I've tried my best to convert the MSSQL version but it's still not working, could anyone help fill in the gaps? I'm not a database expert at all, thanks!

CREATE Function public.RemoveNumericCharacters(Temp VarChar(1000))
Returns VarChar(1000)
AS $$
    Declare NumRange varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, Temp) > 0
        Set Temp = Stuff(Temp, PatIndex(@NumRange, Temp), 1, '')

    Return Temp
End
$$
LANGUAGE SQL;

For a bit more context - the column I need this for is an email column so it will need to convert for example [email protected] to [email protected].

Upvotes: 0

Views: 1065

Answers (1)

user330315
user330315

Reputation:

No need for your own function, this can be achieved using regexp_replace()

select regexp_replace('[email protected]', '[0-9]+', 'g')

Obviously you can put this into a function:

CREATE Function public.removenumericcharacters(p_input text)
 returns text
AS $$
    select regexp_replace(p_input, '[0-9]+', 'g');
$$
LANGUAGE SQL
immutable;

Upvotes: 2

Related Questions