find-products-with-valid-serial-numbers

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code>products</code>

<pre>
+--------------+------------+
| Column Name | Type |
+--------------+------------+
| product_id | int |
| product_name | varchar |
| description | varchar |
+--------------+------------+
(product_id) is the unique key for this table.
Each row in the table represents a product with its unique ID, name, and description.
</pre>

Write a solution to find all products whose description contains a valid serial number pattern. A valid serial number follows these rules:

<ul>
	<li>It starts with the letters SN&nbsp;(case-sensitive).</li>
	<li>Followed by exactly <code>4</code> digits.</li>
	<li>It must have a hyphen (-) followed by exactly <code>4</code> digits.</li>
	<li>The serial number must be within the description (it may not necessarily start at the beginning).</li>
</ul>

Return the result table&nbsp;ordered by <code>product_id</code> in ascending order.

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

products table:

<pre class="example-io">
+------------+--------------+------------------------------------------------------+
| product_id | product_name | description |
+------------+--------------+------------------------------------------------------+
| 1 | Widget A | This is a sample product with SN1234-5678 |
| 2 | Widget B | A product with serial SN9876-1234 in the description |
| 3 | Widget C | Product SN1234-56789 is available now |
| 4 | Widget D | No serial number here |
| 5 | Widget E | Check out SN4321-8765 in this description |
+------------+--------------+------------------------------------------------------+
 </pre>

Output:

<pre class="example-io">
+------------+--------------+------------------------------------------------------+
| product_id | product_name | description |
+------------+--------------+------------------------------------------------------+
| 1 | Widget A | This is a sample product with SN1234-5678 |
| 2 | Widget B | A product with serial SN9876-1234 in the description |
| 5 | Widget E | Check out SN4321-8765 in this description |
+------------+--------------+------------------------------------------------------+
 </pre>

Explanation:

<ul>
	<li>Product 1: Valid serial number SN1234-5678</li>
	<li>Product 2: Valid serial number SN9876-1234</li>
	<li>Product 3: Invalid serial number SN1234-56789 (contains 5 digits after the hyphen)</li>
	<li>Product 4: No serial number in the description</li>
	<li>Product 5: Valid serial number SN4321-8765</li>
</ul>

The result table is ordered by product_id in ascending order.
</div>

find_valid_serial_products

Find Products with Valid Serial Numbers

表：<code>products</code>

<pre>
+--------------+------------+
| Column Name | Type |
+--------------+------------+
| product_id | int |
| product_name | varchar |
| description | varchar |
+--------------+------------+
(product_id) 是这张表的唯一主键。
这张表的每一行表示一个产品的唯一 ID，名字和描述。
</pre>

编写一个解决方案来找到所有描述中 包含一个有效序列号&nbsp;模式的产品。一个有效序列号符合下述规则：

<ul>
	<li>以 SN 字母开头（区分大小写）。</li>
	<li>后面有恰好&nbsp;<code>4</code>&nbsp;位数字。</li>
	<li>接着是一个短横（-）， 短横后面还有另一组 <code>4</code> 位数字</li>
	<li>序列号必须在描述内（可能不在描述的开头）</li>
</ul>

返回结果表以&nbsp;<code>product_id</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

products 表：

<pre class="example-io">
+------------+--------------+------------------------------------------------------+
| product_id | product_name | description |
+------------+--------------+------------------------------------------------------+
| 1 | Widget A | This is a sample product with SN1234-5678 |
| 2 | Widget B | A product with serial SN9876-1234 in the description |
| 3 | Widget C | Product SN1234-56789 is available now |
| 4 | Widget D | No serial number here |
| 5 | Widget E | Check out SN4321-8765 in this description |
+------------+--------------+------------------------------------------------------+
 </pre>

输出：

<pre class="example-io">
+------------+--------------+------------------------------------------------------+
| product_id | product_name | description |
+------------+--------------+------------------------------------------------------+
| 1 | Widget A | This is a sample product with SN1234-5678 |
| 2 | Widget B | A product with serial SN9876-1234 in the description |
| 5 | Widget E | Check out SN4321-8765 in this description |
+------------+--------------+------------------------------------------------------+
 </pre>

解释：

<ul>
	<li>产品 1：有效的序列号&nbsp;SN1234-5678</li>
	<li>产品 2：有效的序列号 SN9876-1234</li>
	<li>产品 3：无效的序列号&nbsp;SN1234-56789（短横后包含 5 位数字）</li>
	<li>产品 4：描述中没有序列号</li>
	<li>产品 5：有效的序列号 SN4321-8765</li>
</ul>

结果表以 product_id 升序排序。
</div>