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

 
location_id  street_address        postal_code  city        state_province  country_id
-----------  --------------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola di Rie  989          Roma                        IT
1100         93091 Calle della Te  10934        Venice                      IT
1200         2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-cho       6823         Hiroshima                   JP
1400         2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500         2011 Interiors Blvd   99236        South San   California      US
1600         2007 Zagora St        50090        South Brun  New Jersey      US
1700         2004 Charade Rd       98199        Seattle     Washington      US
1800         147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
.........
3200         Mariano Escobedo 999  11932        Mexico Cit  Distrito Feder  MX

View the table

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)

Go to:

• PostgreSQL String() Function Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : 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.

Practice Online

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