Earthling Person Earthling Person - 1 month ago 8
SQL Question

SQL Concatenation Query - Four As in Concatenated Name

I am trying to create a view in SQL. We will call this view "A7T8". The view will pull first and last names from the database, and concatenate the first and last names into a field called FullName. However, I only want to select the concatenated names that have a total of at least four As in their first and last names. (Rebecca Aaronson would not be displayed since she only has 3 As, but Harry Flanagan would be displayed.

Currently my code is:

CREATE VIEW A7T8 AS
SELECT FNAME || ' ' || LNAME AS FULLNAME
FROM A7
WHERE Upper(LNAME) LIKE '%A%A%A%A%' OR Upper(FNAME) LIKE '%A%A%A%A%'
ORDER BY LNAME, FNAME;


But this only pulls names that have four As in the first name or four As in the last name. I want it to pull names that have a total of at least four As in the concatenated full name. How do I do this?

Answer

You can simply write:

CREATE VIEW A7T8 AS
SELECT FNAME || ' ' || LNAME AS FULLNAME
FROM A7
WHERE UPPER(FNAME || ' ' || LNAME) LIKE '%A%A%A%A%'
ORDER BY LNAME, FNAME;