How to bring only a certain part of a text in a field

Question

How do I bring only a piece of text into a field in sql server?

I have the query below that in the Description column, it returns a text with a lot of information as for example.

Nome Cliente    Data do ultimo Tramite     Descrição
Teste           25-07-2017                 de para teste ok  descricao do chamado

I want to bring the records that are in the description column, that can come from the word description. How do I? Below is the query:

SELECT
    S.SolID,
    UC.UsuNome [Nome Cliente],
    MAX(T.TraData) [Data do Último Trâmite],
    MAX(CAST(T.Descricao as varchar(max))) [Descrição do Último Trâmite],
    U.UsuNome [Consultor Responsável],
    MAX(M.MotDesc) [Motivo da Pausa]
FROM
    Solicitacao S
    LEFT JOIN Usuario U ON U.UsuID = S.UsuIDResponsavel
    LEFT JOIN Usuario UC ON UC.UsuID = S.UsuIDCliente
    LEFT JOIN StatusMotivo SM ON SM.SMSolID = S.SolID
    INNER JOIN Motivo M ON M.MotID = SM.SMMotID
    INNER JOIN Tramite T on T.SolID = S.SolID and 
             T.TraID = (SELECT TOP 1 
                     X.TraID 
                 FROM
                     Tramite X 
                 WHERE
                    X.SolID = S.SolID
                ORDER BY
                    X.TraData DESC)
WHERE
    S.VencimentoPausado = 1
    AND s.usuidresponsavel = 91258
    AND cast(T.TraData as date) <= cast(DATEADD(day,-2,getdate()) as date)
GROUP BY
    S.SolID,
    UC.UsuNome,
    U.UsuNome

Answer 1

Based on the comment’s response, it follows the code snippet. But he will always get the first occurrence of the term blzz ;) Test to see if it is OK. as I do not have a test bench need validation.

SELECT
    S.SolID,
    UC.UsuNome [Nome Cliente],
    MAX(T.TraData) [Data do Último Trâmite],
    MAX(CAST(T.Descricao as varchar(max))) [Descrição do Último Trâmite],
    U.UsuNome [Consultor Responsável],
    MAX(M.MotDesc) [Motivo da Pausa],
    MAX(SUBSTRING(T.Descricao, PATINDEX('%termoPesquisa%', T.Descricao), LEN(T.Descricao))) [Termo Filtrado]
FROM
    Solicitacao S
    LEFT JOIN Usuario U ON U.UsuID = S.UsuIDResponsavel
    LEFT JOIN Usuario UC ON UC.UsuID = S.UsuIDCliente
    LEFT JOIN StatusMotivo SM ON SM.SMSolID = S.SolID
    INNER JOIN Motivo M ON M.MotID = SM.SMMotID
    INNER JOIN Tramite T on T.SolID = S.SolID and 
             T.TraID = (SELECT TOP 1 
                     X.TraID 
                 FROM
                     Tramite X 
                 WHERE
                    X.SolID = S.SolID
                ORDER BY
                    X.TraData DESC)
WHERE
    S.VencimentoPausado = 1
    AND s.usuidresponsavel = 91258
    AND cast(T.TraData as date) <= cast(DATEADD(day,-2,getdate()) as date)
GROUP BY
    S.SolID,
    UC.UsuNome,
    U.UsuNome