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


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.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv