主页 / coding / 问题 / 79168630
Accepted
Fachry Dzaky
Asked: 2024-11-08 10:41:50 +0800 CST

查询只能采用 varchar 中的数字字符吗？

  • 772

我有一列GL_DESCRIPTION，其值如下

SELECT DISTINCT GL_DESCRIPTION 
FROM database_actual_pemupukan;


PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH
PEMUPUKAN - CPD HIK ROTASI 2  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1  - TRANSPORT
PEMUPUKAN - CPD HIK ROTASI 3  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1  - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - MATERIAL
PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - TRANSPORT

我在 INT 中有一个名为的新列Rotasi，它可以在“ROTASI”之后存储任意数字字符的值

因此对于该要求，我的代码将是这样的：

UPDATE database_actual_pemupukan 
SET Rotasi = SUBSTRING(GL_DESCRIPTION, 
                       PATINDEX('%[0-9]%', GL_DESCRIPTION), 
                       LEN(GL_DESCRIPTION));

返回错误：

消息 245，级别 16，状态 1，第 1 行将
varchar 值 '1 - MATERIAL' 转换为数据类型 int 时转换失败

我也尝试过这个查询

UPDATE database_actual_pemupukan
SET rotasi = CASE 
                 WHEN PATINDEX('%[0-9]%', GL_DESCRIPTION) > 0 
                     THEN CAST(
                               -- Extract the number starting from the first digit
                               SUBSTRING(GL_DESCRIPTION, 
                                         PATINDEX('%[0-9]%', GL_DESCRIPTION), 
                                         CASE 
                                             WHEN PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') = 0 
                                                 THEN LEN(GL_DESCRIPTION) - PATINDEX('%[0-9]%', GL_DESCRIPTION) + 1
                                                 ELSE PATINDEX('%[^0-9]%', GL_DESCRIPTION + 'a') - PATINDEX('%[0-9]%', GL_DESCRIPTION)
                                         END) AS INT)
                 ELSE NULL -- If no number is found, set rotasi to NULL
             END;

此查询返回此错误：

消息 537，级别 16，状态 2，第 1 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

我错在哪儿了？

sql

2 个回答

  1. Best Answer

    你一开始就走对了路，你只需要有条不紊地完成你的提取，例如

    1. 找到第一个数字字符
    2. 查找第一个数字字符后的第一个空格
    3. 然后在它们之间进行子串
    WITH cte1 AS (
    SELECT *
    FROM (
        VALUES
        ('PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - TRANSPORT'),
        ('PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH'),
        ('PEMUPUKAN - CPD HIK ROTASI 2  - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 1  - TRANSPORT'),
        ('PEMUPUKAN - CPD HIK ROTASI 3  - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 1  - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - MATERIAL'),
        ('PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - TRANSPORT')
    ) x (GL_DESCRIPTION)
), cte2 AS (
    SELECT
        GL_DESCRIPTION
        , PATINDEX('%[0-9]%', GL_DESCRIPTION) FirstNumber, LEN(GL_DESCRIPTION) Length
    FROM cte1
), cte3 AS (
    SELECT *
        , CHARINDEX(' ', SUBSTRING(GL_DESCRIPTION, FirstNumber, Length-FirstNumber)) FirstSpace
    FROM cte2
)
SELECT
    GL_DESCRIPTION
    , CONVERT(INT, SUBSTRING(GL_DESCRIPTION, FirstNumber, FirstSpace)) ExtractedNumber
FROM cte3;

    返回：

    GL_描述 抽取数字
    PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - 运输 3
    PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH 2
    PEMUPUKAN - CPD HIK ROTASI 2 - 材料 2
    PEMUPUKAN - CPD HIK ROTASI 1 - 交通 1
    PEMUPUKAN - CPD HIK ROTASI 3 - 材料 3
    PEMUPUKAN - CPD HIK ROTASI 1 - 材料 1
    PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - 材料 2
    PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - 材料 3
    PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - 材料 1
    PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - 运输 1

    数据库Fiddle

    注意：如果需要，您可以将其作为一行程序执行，为了清楚起见，我只是为了分别显示每个计算（并且因为我不喜欢重复代码）。

    • 1

  2. 我们可以在这里使用子字符串技巧，类似于您所尝试的，使用PATINDEX()和REVERSE()：

    WITH cte AS (
    SELECT SUBSTRING(
        GL_DESCRIPTION,
        PATINDEX('%[0-9]%', GL_DESCRIPTION),
        LEN(GL_DESCRIPTION) - PATINDEX('%[0-9]%', REVERSE(GL_DESCRIPTION)) - PATINDEX('%[0-9]%', GL_DESCRIPTION) + 2) AS GL_DESCRIPTION_INT
    FROM database_actual_pemupukan
)

UPDATE cte
SET rotasi = CONVERT(INT, GL_DESCRIPTION_INT);

    上述逻辑的工作原理是，在字符串中找到整数开始和结束的两个点，然后取子字符串。

    这是一个演示，表明提取整数的逻辑是有效的。

    • 1

相关问题