Início / coding / Perguntas / 79168630
Accepted
Fachry Dzaky
Asked: 2024-11-08 10:41:50 +0800 CST

Consulta que pode receber apenas caracteres numéricos em varchar?

  • 772

Eu tenho uma coluna GL_DESCRIPTIONque tem valores como este

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

Tenho uma nova coluna em INT chamada Rotasique pode armazenar qualquer valor numérico após 'ROTASI'

Então, para esse requisito, meu código seria assim:

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

Retorna um erro:

Msg 245, Nível 16, Estado 1, Linha 1
Falha na conversão ao converter o valor varchar '1 - MATERIAL' para o tipo de dados int

Também tentei esta consulta

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;

Esta consulta retorna este erro:

Msg 537, Nível 16, Estado 2, Linha 1
Parâmetro de comprimento inválido passado para a função LEFT ou SUBSTRING.

Onde estou errado?

sql

2 respostas

  1. Best Answer

    Você começou no caminho certo, você só precisa ser metódico ao concluir sua extração, por exemplo

    1. Encontre o primeiro caractere numérico como você fez
    2. Encontre o primeiro espaço após o primeiro caractere numérico
    3. Então substring entre eles
    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;

    Retornos:

    GL_DESCRIÇÃO Número extraído
    PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - TRANSPORTE 3
    PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - UPAH 2
    PEMUPUKAN - CPD HIK ROTASI 2 - MATERIAL 2
    PEMUPUKAN - CPD HIK ROTASI 1 - TRANSPORTE 1
    PEMUPUKAN - CPD HIK ROTASI 3 - MATERIAL 3
    PEMUPUKAN - CPD HIK ROTASI 1 - MATERIAL 1
    PEMUPUKAN - CPD HIK ROTASI 2 EMDEK - MATERIAL 2
    PEMUPUKAN - CPD HIK ROTASI 3 EMDEK - MATERIAL 3
    PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - MATERIAL 1
    PEMUPUKAN - CPD HIK ROTASI 1 EMDEK - TRANSPORTE 1

    Fiddle de DB

    Observação: você pode fazer isso em uma linha, se desejar. Eu apenas mostrei cada cálculo separadamente para maior clareza (e porque não gosto de duplicar código).

    • 1

  2. Podemos usar um truque de substring aqui, semelhante ao que você estava tentando, com PATINDEX()e 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);

    A lógica acima funciona encontrando os dois pontos na string onde o inteiro começa e termina e, em seguida, pegando uma substring.

    Aqui está uma demonstração mostrando que a lógica para extrair o inteiro está funcionando.

    • 1

relate perguntas