sql server 提取字段中的字符数字

原创

mob649e816aeef7 2024-12-12 03:53:09 ©著作权

文章标签 SQL 数据 Server 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob649e816aeef7的原创作品，请联系作者获取转载授权，否则将追究法律责任

如何在 SQL Server 中提取字段中的字符数字

在日常开发中，我们常常需要从字符串中提取出数字。这在数据分析、ETL（提取、转换、加载）流程等多个场景中都非常常见。本文将详述如何在 SQL Server 中实现这一目标。

流程概述

下面是提取字符数字的步骤流程：

步骤	描述
1.	创建示例表并插入示例数据
2.	使用 SQL 查询提取数字
3.	运行查询并验证结果

步骤详情

1. 创建示例表并插入示例数据

首先，您需要创建一个示例表来存储待处理的数据。我们将创建一个名为 Products 的表，并插入一些示例数据。

-- 创建 Products 表
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100)
);

-- 插入示例数据
INSERT INTO Products (ProductID, ProductName) VALUES
(1, 'Apple 123'),
(2, 'Orange 4567'),
(3, 'Banana 89'),
(4, 'Peach 10g');

此段代码执行了以下操作：

创建表：定义 Products 表，包含 ProductID 和 ProductName。
插入数据：往表中插入了四个产品，其中产品名包含字符和数字。

2. 使用 SQL 查询提取数字

接下来，我们将使用 PATINDEX 和 STUFF 函数来提取产品名中的数字。

-- 提取 ProductName 中的数字并返回
SELECT 
    ProductID,
    ProductName,
    -- 使用自定义的函数提取数字
    dbo.ExtractNumbers(ProductName) AS ExtractedNumbers
FROM 
    Products;

提取数字的自定义函数示例：

我们可以使用 SQL Server 的用户定义函数 (UDF) 来处理字符串，提取其中的数字。

CREATE FUNCTION dbo.ExtractNumbers (@input NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @result NVARCHAR(100) = '';
    DECLARE @i INT = 1;

    WHILE @i <= LEN(@input)
    BEGIN
        -- 判断当前字符是否为数字（ASCII 48-57）
        IF SUBSTRING(@input, @i, 1) LIKE '[0-9]'
        BEGIN
            SET @result = @result + SUBSTRING(@input, @i, 1);
        END
        SET @i = @i + 1;
    END

    RETURN @result;
END;

说明：

LEN：获取输入字符串的长度。
SUBSTRING：根据索引获取字符串中的字符。
LIKE '[0-9]'：用于判断当前字符是否为数字。

3. 运行查询并验证结果

运行上述查询，您将看到每个产品的 ID、名称以及提取出的数字。

类图与序列图

为了帮助更好地理解整个过程，下面我们将展示类图和序列图。

类图

classDiagram
    class Products {
        +int ProductID
        +string ProductName
    }
    class ExtractNumbers {
        +string Process(string input)
    }
    Products --> ExtractNumbers : uses

序列图

sequenceDiagram
    participant User
    participant SQLServer
    participant ExtractNumbers

    User->>SQLServer: Select ProductData
    SQLServer->>ExtractNumbers: Process(ProductName)
    ExtractNumbers-->>SQLServer: Return(ExtractedNumbers)
    SQLServer-->>User: Display(ProductID, ProductName, ExtractedNumbers)