i have a table Person{name, surname, location} and i want to select those surname that their first consonant is the same. For example: "Adams" and "Eodens" their first consonant is "d"
CodePudding user response：
You should write a function(see tutorial) that returns the first consonant of a given string argument. Ensure to mark it as IMMUTABLE function when you create it. Then you could create a computed aka generated column based on that surname column, then you should do a self join on that table to select those records that have the same value for that computed column.
CodePudding user response：
You can use a (case insensitive) regular expression match operation.
SELECT *
FROM person
WHERE surname ~* '^[^bcdfghjklmnpqrstvwxyz]*d';
The ^ at the beginning of the pattern matches the beginning of the string. [^bcd...] is a negated (because of the ^) class to match all characters that aren't consonants -- because of the negation it lists all consonants -- (that way not only vowel letters match but other characters like interpunctation too). * quantifies these characters to an arbitrary count. Finally d is the character you search for. So from the beginning of the string there can be an arbitrary number of non consonants followed by d.
CodePudding user response：
You can extract the first consonant using a regular expression, e.g.:
substring(surname from '[bcdfghjklmnpqrstvwxyz]')
This can be used with an EXISTS condition to find all rows that have the same value for that expression:
select p1.*, substring(p1.surname from '[bcdfghjklmnpqrstvwxyz]') as first_consonant
from person p1
where exists (select *
from person p2
where substring(p1.surname from '[bcdfghjklmnpqrstvwxyz]') = substring(p2.surname from '[bcdfghjklmnpqrstvwxyz]')
and p1.id <> p2.id)
order by first_consonant
The id column is assumed to be a primary (or unique) key in order to distinguish the rows.