PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to remove specific characters from a string in SQL

Suppose I have a string in my table, I now want to remove all characters that are the same as the first character (case-insensitive) from that string.

For example:

uJnMus onsyk uyT Uaszs     ->   JnMs onsyk yT aszs

@uJnMus o@nsyk uyT Ua@szs  ->   uJnMus onsyk uyT Uaszs

I know there is a very simple method, but it is not universally applicable (it requires adjusting the expression u or @ or … based on the string):

SELECT regexp_replace(content, '^u|u', '', 'ig')
FROM string;

SELECT regexp_replace(content, '^@|@', '', 'ig')
FROM string;

I tried another method to apply to all strings:

SELECT regexp_replace(content, '^(.)|1', '', 'ig')
FROM string;

But the result is not as expected, it only removes the first character. I’m not particularly good at regular expressions.

Could anyone give me some help? (any other methods would also be appreciated)

Tags:

3

Answers


  1. 0

    Use substr to extract the first character, then replace both the lower and upper form of the character with the empty string:

    with foo (t) as (values ('uJnMus onsyk uyT Uaszs'), ('@uJnMus o@nsyk uyT Ua@szs'))
select replace(replace(t, lower(substr(t, 1, 1)), ''), upper(substr(t, 1, 1)), '') from foo;

    Output:

            replace
------------------------
 JnMs onsyk yT aszs
 uJnMus onsyk uyT Uaszs
(2 rows)
    Login or Signup to reply.
  2. 0

    This method can meet your needs:

    SELECT REGEXP_REPLACE(content, SUBSTR(content, 1, 1), '', 'ig')
FROM string;

    https://dbfiddle.uk/vl7OfVUX

    Login or Signup to reply.
  3. 0

    Use left() and replace() or translate():

    Case sensitive:

    SELECT replace(content, left(content, 1), '')
FROM   string;

    Case insensitive:

    SELECT translate(content, upper(left(content, 1))
                       || lower(left(content, 1)), '')
FROM   string;

    fiddle

    Simpler and cheaper than regular expressions.
    Works for any character.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search