PostgreSQL String() Function: Get the locations that has on and below the minimum character length of street address

10. Write a query to get the information about those locations which contain the characters in its street address is on and below the minimum character length of street_address.

Sample Solution:

Code:

-- This SQL query retrieves all columns from the locations table where the length of the street_address is less than or equal to the minimum length of street_address across all locations.

SELECT * -- Selects all columns from the locations table
FROM locations -- Specifies the table from which to retrieve data, in this case, the locations table
WHERE LENGTH(street_address) <= ( -- Filters the rows to include only those where the length of street_address is less than or equal to the minimum length of street_address
    SELECT MIN(LENGTH(street_address)) -- Finds the minimum length of street_address across all locations
    FROM locations -- Specifies the table from which to retrieve data for the subquery, which is also the locations table
);

Explanation:

• This SQL query retrieves data from the locations table.
• The outer SELECT statement selects all columns from the locations table.
• The FROM clause specifies the table from which to retrieve the data, which is the locations table.
• The WHERE clause filters the rows to include only those where the length of the street_address column is less than or equal to the minimum length of street_address across all locations.
• The subquery (SELECT MIN(LENGTH(street_address)) FROM locations) calculates the minimum length of street_address across all locations.
• The LENGTH() function is used to calculate the length of the street_address column.
• The result set will contain all columns for rows where the length of the street_address is less than or equal to the minimum length of street_address across all locations.

Sample table: locations

Output:

pg_exercises=# SELECT *
pg_exercises-# FROM locations
pg_exercises-# WHERE LENGTH(street_address)<=(
pg_exercises(# SELECT  MIN(LENGTH(street_address))
pg_exercises(# FROM locations);

 location_id | street_address | postal_code |      city       | state_province | country_id
-------------+----------------+-------------+-----------------+----------------+------------
        1600 | 2007 Zagora St | 50090       | South Brunswick | New Jersey     | US
        2400 | 8204 Arthur St |             | London          |                | UK
(2 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to extract the last four characters of phone numbers.
Next: Write a query to display the first word in the job title if the job title contains more than one words.