w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.