CODEWITHSUNDEEP
sqlstringpostgresql
Luffydude
Luffydude

Reputation: 792

How to remove numbers in a string but keep the spaces in postgresql?

So I have a column with the following values that some times has a number in the beginning

turtle boat
10 banana split
lord Thanos
23 macbook

How to query to remove numbers and the space in front of the numbers, but not the space in the middle of the string?

Intended output:

turtle boat
banana split
lord Thanos
macbook

Query I tried from another question in here:

select regexp_replace(mycolumn, '[^[:alpha:]]', '', 'g')

Problem with this query is that it completely removes all spaces

Upvotes: 6

Views: 11253

Answers (2)

guido
guido

Reputation: 19194

This will remove the leading digits and the space:

select regexp_replace('10 banana split', '^\d+ ', '');

/^\d+[ ]/
 ^ asserts position at start of a line
  \d+ matches a digit (equal to [0-9])
    + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)
     [ ] matches the character ' ' literally (case sensitive)

The g modifier, for 'global', is why in your regex all spaces were removed, as matched as non-alpha characters.

fiddle here

Upvotes: 4

klin
klin

Reputation: 121644

You can exclude all characters except of letters and spaces. Additionally, use trim() to remove leading or trailing spaces:

with my_data(mycolumn) as (
values
    ('turtle boat'),
    ('10 banana split'),
    ('lord Thanos'),
    ('23 macbook')
)

select trim(regexp_replace(mycolumn, '[^[:alpha:]\s]', '', 'g'))
from my_data

    btrim     
--------------
 turtle boat
 banana split
 lord Thanos
 macbook
(4 rows)

Upvotes: 10

Related Questions