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:

SELECT * 
FROM locations 
WHERE LENGTH(street_address)<=(
SELECT  MIN(LENGTH(street_address)) 
FROM 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.