w3resource

AdventureWorks Database: Find the number of characters in the FirstName column


63. From the following table write a query in SQL to select the number of characters and the data in FirstName for people located in Australia.

Sample table: Sales.vindividualcustomer
businessentityid|title|firstname|middlename|lastname    |suffix|phonenumber        |phonenumbertype|emailaddress                   |emailpromotion|addresstype|addressline1                    |addressline2        |city                 |stateprovincename  |postalcode|countryregionname|demographics|
----------------+-----+---------+----------+------------+------+-------------------+---------------+-------------------------------+--------------+-----------+--------------------------------+--------------------+---------------------+-------------------+----------+-----------------+------------+
            1699|Mr.  |David    |R.        |Robinett    |      |238-555-0100       |Home           |david22@adventure-works.com    |             1|Home       |Pappelallee 6667                |                    |Solingen             |Nordrhein-Westfalen|42651     |Germany          |[XML]       |
            1700|Ms.  |Rebecca  |A.        |Robinson    |      |648-555-0100       |Cell           |rebecca3@adventure-works.com   |             0|Home       |1861 Chinquapin Ct              |                    |Seaford              |Victoria           |3198      |Australia        |[XML]       |
            1701|Ms.  |Dorothy  |B.        |Robinson    |      |423-555-0100       |Cell           |dorothy3@adventure-works.com   |             2|Home       |4693 Mills Dr.                  |                    |Geelong              |Victoria           |3220      |Australia        |[XML]       |
            1702|Ms.  |Carol Ann|F.        |Rockne      |      |439-555-0100       |Cell           |carolann0@adventure-works.com  |             0|Home       |1312 Skycrest Drive             |                    |Lancaster            |England            |LA1 1LN   |United Kingdom   |[XML]       |
            1703|Mr.  |Scott    |M.        |Rodgers     |      |989-555-0100       |Cell           |scott10@adventure-works.com    |             0|Home       |9860 Brookview Drive            |                    |East Brisbane        |Queensland         |4169      |Australia        |[XML]       |
            1704|Mr.  |Jim      |          |Rodman      |      |899-555-0100       |Home           |jim4@adventure-works.com       |             0|Home       |2377 Joyce Dr                   |                    |Esher-Molesey        |England            |EM15      |United Kingdom   |[XML]       |
            1705|Mr.  |Eric     |          |Rothenberg  |      |326-555-0100       |Home           |eric9@adventure-works.com      |             0|Home       |9277 Country View Lane          |                    |Concord              |California         |94519     |United States    |[XML]       |
            1706|Mr.  |Michael  |L.        |Rothkugel   |      |358-555-0100       |Cell           |michael22@adventure-works.com  |             0|Home       |3552 Mildred Ln.                |                    |St. Leonards         |New South Wales    |2065      |Australia        |[XML]       |
            1707|Mr.  |Pablo    |          |Rovira Diez |      |786-555-0100       |Cell           |pablo0@adventure-works.com     |             0|Home       |15, rue Descartes               |                    |East Brisbane        |Queensland         |4169      |Australia        |[XML]       |
            1708|Ms.  |Linda    |R.        |Rousey      |      |369-555-0100       |Home           |linda10@adventure-works.com    |             1|Home       |5966 Sepulveda Ct.              |                    |Seaford              |Victoria           |3198      |Australia        |[XML]       |
			- more -

Click to view Full table

Sample Solution:

-- Selecting the length of the 'FirstName' column, along with 'FirstName' and 'LastName'
SELECT LENgth(FirstName) AS Length, FirstName, LastName   

-- From the Sales schema's vIndividualCustomer view
FROM Sales.vIndividualCustomer  

-- Filtering the results to include only rows where the 'CountryRegionName' column is 'Australia'
WHERE CountryRegionName = 'Australia';

Explanation:

  • The SQL query retrieves data from the vIndividualCustomer view within the Sales schema.
  • It calculates the length of the FirstName column using the LENGTH() function and aliases the result as 'Length'.
  • The LENGTH() function returns the number of characters in a string.
  • The FirstName and LastName columns are also selected.
  • The WHERE clause filters the results to include only rows where the value of the CountryRegionName column is 'Australia'.

Sample Output:

length|firstname|lastname |
------+---------+---------+
     3|Jon      |Yang     |
     6|Eugene   |Huang    |
     5|Ruben    |Torres   |
     7|Christy  |Zhu      |
     9|Elizabeth|Johnson  |
     5|Julio    |Ruiz     |
     5|Janet    |Alvarez  |
     5|Marco    |Mehta    |
     3|Rob      |Verhoff  |
     7|Shannon  |Carlson  |
     9|Jacquelyn|Suarez   |
	 ...

SQL AdventureWorks Editor:


Practice Online



Contribute your code and comments through Disqus.

Previous: Return the five leftmost characters of each product name.
Next: Provide the connections in Australia and the length of FirstName.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.