SQL to load only part of a string

Question

I’d like some help. I have the following query below, however, I need that in the DE column bring only the texts after the word "Exclusion", and in the PARA column bring only 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 whole line and you are 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 help?

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 it helps you.

Answer 2

In the question you did not specify in which database you are running, this query I did using SQL Server. But basically 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 more or less 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'
    )
)