如何用SQL Server查询提取某列首尾数字间的内容（含首尾数字）

需求说明 #

需要从指定列的字符串中提取第一个x （x加空格）之后到最后一个数字之间的内容，包含首尾数字。示例如下：

• Plastic Bottle 250ml x 24 Loosepack Dust → 24
• Plastic Bottles 250ml x 6 x 4 Sportscap → 6 x 4
• Plastic Bottles 300ml x 24 → 24

目标列唯一值列表 #

Plastic Bottle 250ml x 24 Loosepack Dust
Plastic Bottles 300ml x 24
Cans 500ml x 24
Plastic Bottles 250ml x 24
Glass 355ml x 6 x 4
Pail 5L x 1
Cans 163ml x 24
Glass 750ml x 6
Medium Plastic Jars 400g x 12
Plastic Bottles 250ml x 6 x 4 Sportscap
Plastic Bottles 1.25L x 4 x 3
Cans 250ml x 15
Cans 355ml x 24
Cans 330ml x 4 x 6
Plastic Bottles 1L x 4
Cans 200ml x 6 x 4 Slimline
Plastic Bottles 2L x 6
Large Plastic Jars 560g x 12
Plastic Bottles 1.5L x 6
Packet 85g x 12
Plastic Bottles 5L x 3
Glass 290ml x 15
Plastic Bottles 600ml x 4 x 3
Cans 375ml x 24 Multipack Cube
Cans 330mL x 6 x 4pk Sleek
Plastic Bottles 585g x 12
Plastic Bottles 1L x 6 x 2
Glass 300ML x 15
Glass 275ml x 10 x 3
Glass 300ml x 24
Cans 375ml x 20 Slab
Plastic Bottles 250ml x 4 x 6
Glass 330ml x 24
Party Packs
Glass 340ml x 4 x 6
Bag-in-Box 15L x 1
Packet 85g x 4 x 18 Multipack
Plastic Bottles 2L x 8
Glass 325ml x 12
Glass Jar 380g x 12
Bags 1.26KG X 6
Drum 220kg x 1
Pouch 118ML X 8PK X 2
Glass 275ml x 15
Cans 250ml x 12 x 2
Plastic Bottles 600ml x 6 x 4
Cans 375ml x 24 Cube
Cans 250ml x 6 x 4 Slimline
Glass Jar 340g x 12
Cans 250ml x 12
Plastic Bottles 500ml x 8
Plastic Bottles 375ml x 12
Cans 946ml x 12
Glass 1L x 6
Glass 315ml x 4 x 6
Tetrapack 500ml x 8
Cans 375ml x 12 x 2
SACHET 42G X 10 X 6
Plastic Bottles 250ml x 6 x 4
Plastic Bottles 350ml x 6
Plastic Bottles 1.25L x 12
Plastic Bottles 300ml x 12
Glass 585g x 12
Cans 375ml x 12 Multipack
Glass 200ml x 24
Cans 250ml x 24 Slimline
Half Pallet
Plastic Bottles 2L x 108
Tetrapack 250ml x 24
Plastic Bottles 150ml x 6 x 4 Sportscap
Glass 300ML X 12
Cans 285ml x 24
Plastic Bottles 220g x 12
Cans 500ml x 12 x 2
Glass 330ml x 6 x 4
Cans 375ml x 30 Cube
Glass 340ml x 24
Cans 330ml x 24 cube
Glass 750ml x 8
Glass Jar 450g x 12
Plastic Bottles 48ml x 12
Plastic Bottles 1.25L x 6
Cans 680g x 12
Glass 330ml x 12
Plastic Bottles 500ml x 12
Packet 2kg x 6
Plastic Bottles 1.5L x 8
Cans 330ml x 8
Large Plastic Jars 560g x 6
Plastic Bottles 1L x 4 x 2
Cans 250ml x 6 x 4
Other
Plastic Bottles 330ml x 24
Glass 700ml x 12
Plastic Bottles 3L x 4
Point of Sale
Pail 2.5kg x 6
Plastic Bottles 400ml x 12
Plastic Bottles 500ml x 18
Cans 440ml x 6 x 4
Plastic Bottles 400g x 2 x 8
Plastic Bottles 500ml x 24 Sportscap
Cans 375ml x 36 Cube
Cans 250ml x 12 Squat
Cans 355ml x 4pk x 6 sleek
Cans 300ml x 4 x 6 Slimline
Plastic Bottles 400g x 12
Plastic Bottles 600ml x 18
Extra Large Plastic Jars 1.75kg x 6
Tetrapack 250ml x 6 x 4
Cans 330ml x 10 x 3
Plastic Bottles 4L x 3

查询语句实现 #

假设目标列名为product_description，表名为products，以下是满足需求的SQL查询：

SELECT 
    product_description,
    CASE 
        WHEN CHARINDEX('x ', product_description) = 0 THEN NULL
        ELSE 
            LEFT(
                SUBSTRING(product_description, CHARINDEX('x ', product_description) + 2, LEN(product_description)),
                LEN(SUBSTRING(product_description, CHARINDEX('x ', product_description) + 2, LEN(product_description))) 
                - PATINDEX('%[0-9]%', REVERSE(SUBSTRING(product_description, CHARINDEX('x ', product_description) + 2, LEN(product_description)))) 
                + 1
            )
    END AS extracted_content
FROM 
    products;

语句说明 #

1. CHARINDEX('x ', product_description)：定位字符串中第一个x （x加空格）的位置，若不存在则返回NULL。
2. SUBSTRING(...)：提取x 之后的所有子串。
3. REVERSE(...) + PATINDEX('%[0-9]%', ...)：反转x 后的子串，找到第一个数字的位置，以此确定原串中最后一个数字的位置。
4. LEFT(...)：从x 后的子串中截取到最后一个数字的位置，得到目标内容。

内容的提问来源于stack exchange，提问作者Krishnamohan