Search code examples
regexpostgresqlsubstringstring-function

Postgresql substring using regex

I need to run a postgresql query to get names from database but I need to sort these names alphabetically. The names that I am gettign from database are as follows:

(123) Jone Lee
(22) Hans Hee
2 Dean Alloni
Alen Khan

I need to output to be

Alen Khan
2 Dean Alloni
(22) Hans Hee
(123) Jone Lee

I tried the following psql query:

select name from table order by substring(name, E'\\W+\ +(.*)');
select name from table order by substring(name, E'\\(?\\w+?\\)?\ +?(.*)');

My problem if the name is Alen Khan, it only return Khan, so I get:

Khan
Dean Alloni
Hans Hee
Jone Lee

Any Help would be appreciate,

kind regards

Solution 
  • select name 
from table 
order by substring(name, E'[a-zA-Z]+')

    Edit as per OP's comment

    select name
from table order by regexp_replace(name, '[^a-zA-Z]', '', 'g')