sql - Postgres: order data by part of string -

sql - Postgres: order data by part of string -

i have column name represents person's name in next format:

firstname [middlename] lastname [, sr.|jr.]

for, example:

john smith john j. smith john j. smith, sr.

how can order items lastname?

a correct , faster version this:

select * tbl order substring(name, '([^[:space:]]+)(?:,|$)')

or:

order substring(name, e'([^\\s]+)(?:,|$)')

or even:

order substring(name, e'([^\\s]+)(,|$)') explain

[^[:space:]]+ .. first (and longest) string consisting of 1 or more non-whitespace characters. (,|$) .. terminated comma or end of string.

the lastly 2 examples utilize escape-string syntax , class-shorthand \s instead of long form [[:space:]] (which loses outer level of brackets when within character class).

we don't have utilize non-capturing parenthesis (?:) after part want extract, because (quoting manual):

.. if pattern contains parentheses, portion of text matched first parenthesized subexpression (the 1 left parenthesis comes first) returned.

test select substring(name, '([^[:space:]]+)(?:,|$)') (values ('john smith') ,('john j. smith') ,('john j. smith, sr.') ,('foo bar smith, jr.') ) x(name)

sql postgresql