w3resource

AdventureWorks Database: Repeat the 0 character four times before productnumber

SQL Query - AdventureWorks: Exercise-184 with Solution

184. From the following table write a query in SQL to repeat the 0 character four times before productnumber. Return name, productnumber and newly created productnumber.

Sample table: Production.Product

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:

SELECT Name,  
   productnumber ,
   concat(REPEAT('0', 4) , productnumber) AS fullProductNumber
FROM Production.Product  
ORDER BY Name;

Sample Output:

name                            |productnumber|fullproductnumber|
--------------------------------+-------------+-----------------+
Adjustable Race                 |AR-5381      |0000AR-5381      |
All-Purpose Bike Stand          |ST-1401      |0000ST-1401      |
AWC Logo Cap                    |CA-1098      |0000CA-1098      |
BB Ball Bearing                 |BE-2349      |0000BE-2349      |
Bearing Ball                    |BA-8327      |0000BA-8327      |
Bike Wash - Dissolver           |CL-9009      |0000CL-9009      |
Blade                           |BL-2036      |0000BL-2036      |
Cable Lock                      |LO-C100      |0000LO-C100      |
Chain                           |CH-0234      |0000CH-0234      |
Chain Stays                     |CS-2812      |0000CS-2812      |
Chainring                       |CR-7833      |0000CR-7833      |
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Return the orders that have sales on or after December 2011.
Next: Null special offers will return MaxQty as zero.


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.