AdventureWorks Database: Remove the substring 'HN' from the start of the productnumber

SQL Query - AdventureWorks: Exercise-67 with Solution

67. From the following table write a query in SQL to remove the substring 'HN' from the start of the column productnumber. Filter the results to only show those productnumbers that start with "HN". Return original productnumber column and 'TrimmedProductnumber'.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting the 'productnumber' column and applying LTRIM() function to remove leading characters 'H' and 'N', 
-- and aliasing the result as "TrimmedProductnumber"
SELECT productnumber, LTRIM(productnumber , 'HN') as "TrimmedProductnumber"

-- From the Production schema's Product table
FROM production.product

-- Filtering the results to include only rows where the leftmost 2 characters of 'productnumber' are 'HN'
WHERE left(productnumber,2)='HN';

Explanation:

• The SQL query retrieves data from the product table within the production schema.
• It selects the 'productnumber' column.
• The LTRIM() function is applied to the 'productnumber' column to remove leading characters 'H' and 'N'.
• The result of the LTRIM() function is aliased as "TrimmedProductnumber".
• The WHERE clause filters the results to include only rows where the leftmost 2 characters of 'productnumber' are 'HN'.
• The left() function extracts a specified number of characters from the left side of a string.

Sample Output:

productnumber|TrimmedProductnumber|
-------------+--------------------+
HN-1024      |-1024               |
HN-1032      |-1032               |
HN-1213      |-1213               |
HN-1220      |-1220               |
HN-1224      |-1224               |
HN-1420      |-1420               |
HN-1428      |-1428               |
HN-3410      |-3410               |
HN-3416      |-3416               |
HN-3816      |-3816               |
HN-3824      |-3824               |
HN-4402      |-4402               |
HN-5161      |-5161               |
HN-5162      |-5162               |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Remove spaces from the beginning of a string.
Next: Repeat a zero four times in front of a production line.