6

I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To know all appearing characters I need to get a list of them from somewhere.

Is it possible to get a distinct list of characters appearing in a column? What would be the way in PostgreSQL?

For example I have a table with names:

id | name
-----------
1  | Peter
2  | Andrea
3  | Bob

The resulting list would be

petrandbo

The order is not important but could be alphabetical

  • Sounds like a custom aggregate. – Colin 't Hart Nov 26 '14 at 9:47
10
select string_agg(c,'')
from (
  select distinct regexp_split_to_table(lower(name),'') as c
  from data
) t

The inner select generates one row for each character, and the outer then aggregates that to a long string.

If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.