CODEWITHSUNDEEP
sqlpostgresql
mikasa
mikasa

Reputation: 180

how to find the first consonant sql in varchar

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"

Upvotes: 0

Views: 307

Answers (3)

user330315
user330315

Reputation:

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.

Online example

Upvotes: 0

Anand Sowmithiran
Anand Sowmithiran

Reputation: 2920

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.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 0

Related Questions