SQL to load only part of a string [closed]

Question

SQL to load only part of a string [closed]

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (-3 votes)

-1

I would like some help. I have the following query below, however, I need in the column DE to bring only the texts after the word "Exclusion", and in column PARA only bring the text from the word "Inclusion". However, today, he is bringing the whole text and I need only what comes "Exclusion: XXXXXXX" and "Inclusion: YYYYY".

You are loading the entire line and it is not respecting the limit that was created.

Code:

SELECT
"DATA",
CONTRATO,
TIPO_CONTRATO,
STATUS_CONTRATO,
MODALIDADE_FATURAMENTO,
PROMOCAO,
SUBSTR(observacao,INI) AS DE,
SUBSTR(observacao2,LM) AS PARA,
LOGIN,
USUARIO,
AREA
FROM 
(SELECT 
BASE.*,
INSTR(observacao2,' Inclusão: ',1) as LM,
INSTR(observacao2, ' Exclusão: ',1) as ini
FROM
(
SELECT 
"ID",
A."DATA",
A.CONTRATO,
NUMERO_CONTRATO,
ACAO,
DESCRICAO_ACAO,
OBSERVACAO,
NUMERO_PROTOCOLO,
LOGIN,
USUARIO,
A.AREA,
B.PROMOCAO,
TIPO_CONTRATO,
STATUS_CONTRATO,
MODALIDADE_FATURAMENTO,
replace (replace(a.OBSERVACAO,'Inclusão:',' Inclusão:'),'Exclusão:','     Exclusão:  ') as observacao2
 from DBMDTH.vm_dth_notas_contrato A, DBMDTH.VM_DTH_USUARIO_APLICA_PROMOCAO B, DBMDTH.VM_DBM_CONTRATO E where A."DATA" BETWEEN  '01/07/18' and '31/07/18'

Can you help?

sql sqldeveloper

asked by anonymous 01.08.2018 / 16:59

3 answers

What is the purpose of illuminate in laravel? Do a "Update" with a sum of a number that is as a character

score 2 · Answer 1

The SUBSTR command needs 3 arguments:

SUBSTRING( string, start_position, length )

In this case the lines below can be replaced by:

SUBSTR(observacao,INI) AS DE,
SUBSTR(observacao2,LM) AS PARA,

To:

SUBSTR(observacao, 10, LEN(observacao) - 10) AS DE,
SUBSTR(observacao2, 10, LEN(observacao2) - 10) AS PARA,

Please see if this helps you.

score 0 · Answer 2

In the question you did not specify which bank is running, this query did using SQL Server. But basically it does the substring up to the ":"

SELECT
    T.Texto
    ,SUBSTRING(T.Texto, 1, CHARINDEX(':', t.Texto))
FROM
(
    SELECT
        'Inclusão: YYYYY' AS Texto
) AS T

adapting to your case would look something like this

    SELECT
    "DATA",
    CONTRATO,
    TIPO_CONTRATO,
    STATUS_CONTRATO,
    MODALIDADE_FATURAMENTO,
    PROMOCAO,
    --SUBSTR(observacao,INI) AS DE,
    SUBSTRING(observacao, 1, CHARINDEX(':', observacao)) AS DE,
    --SUBSTR(observacao2,LM) AS PARA,
    SUBSTRING(observacao2, 1, CHARINDEX(':', observacao2)) AS PARA,
    LOGIN,
    USUARIO,
    AREA
FROM 
(
    SELECT 
        BASE.*,
        INSTR(observacao2,' Inclusão: ',1) as LM,
        INSTR(observacao2, ' Exclusão: ',1) as ini
    FROM
    (
        SELECT 
            "ID",
            A."DATA",
            A.CONTRATO,
            NUMERO_CONTRATO,
            ACAO,
            DESCRICAO_ACAO,
            OBSERVACAO,
            NUMERO_PROTOCOLO,
            LOGIN,
            USUARIO,
            A.AREA,
            B.PROMOCAO,
            TIPO_CONTRATO,
            STATUS_CONTRATO,
            MODALIDADE_FATURAMENTO,
            replace (replace(a.OBSERVACAO,'Inclusão:',' Inclusão:'),'Exclusão:','     Exclusão:  ') as observacao2
        from
            DBMDTH.vm_dth_notas_contrato A, 
            DBMDTH.VM_DTH_USUARIO_APLICA_PROMOCAO B, 
            DBMDTH.VM_DBM_CONTRATO E 
        where 
            A."DATA" BETWEEN  '01/07/18' and '31/07/18'
    )
)

score -3 · Answer 3

Maybe this

SELECT SUBSTR ('TEST: VALUE1 TEST2: VALOR2', INSTR ('TEST: VALUE1 TEST2: VALUE2', 'TEST'), INSTR ('TEST: VALUE1 TEST2: VALOR2', 'TEST2') - 1) FROM DUAL

SELECT SUBSTR ('TEST: VALUE1 TEST2: VALOR2'), INSTR ('TEST: VALUE1 TEST2: VALUE2', 'TEST2') - 1, LENGTH >