Query to catch word after given character

Question

Good morning, I need a way to get all the rest of a string after a certain character, for example, I have the following database

I always need to find what’s left of the string after the last "»" that is, for the first line I needed to pick up all "Access to the system" because there is no "»"

On the second Row I need to pick up "Farmácia Popular" for it is after the ULTIMO "»"

So far I have the following query

select TOP 10 COUNT(SolID) as soma, SolCaminho as caminho
from Solicitacao where
DATEPART(m, SolData) = DATEPART(m, DATEADD(m, 0, getdate()))
AND DATEPART(yyyy, SolData) = DATEPART(yyyy, DATEADD(m, 0, getdate()))
and UsuIDGrupoRespConclusao = 2655
group by SolCaminho order by soma desc

I tried to do with SUBSTRING with Charindex but did not succeed.

Answer 1

You did not specify which word you want to compare or which column to search for, adapt and test:

with t as (SELECT 'Inicio bla bla bla Meio bla Fim bla ww Fim 123 bla dihfidfh' as teste)

select SUBSTRING(teste, (len(teste) - CHARINDEX(REVERSE('Fim'), REVERSE(teste)) - 1), len(teste)) FROM t

Edited... again, I included a CASE:

select TOP 10 COUNT(SolID) as soma,
        CASE WHEN SolCaminho LIKE '% >> %'
                THEN SUBSTRING(SolCaminho, (len(SolCaminho) - CHARINDEX(REVERSE('>> '), REVERSE(SolCaminho)) + 2), len(SolCaminho))
                ELSE SolCaminho
           END as caminho
from Solicitacao where
DATEPART(m, SolData) = DATEPART(m, DATEADD(m, 0, getdate()))
AND DATEPART(yyyy, SolData) = DATEPART(yyyy, DATEADD(m, 0, getdate()))
and UsuIDGrupoRespConclusao = 2655
group by CASE WHEN SolCaminho LIKE '% >> %'
                THEN SUBSTRING(SolCaminho, (len(SolCaminho) - CHARINDEX(REVERSE('>> '), REVERSE(SolCaminho)) + 2), len(SolCaminho))
                ELSE SolCaminho
           END
order by soma desc

Note that I used the function on group by also, group after treating, if necessary use:

group by SolCaminho

Answer 2

I was researching about CHARINDEX on the Internet and ended up here, so I tried seek a solution to the problem since it has apparently not been solved.

REVERSE to invert the characters and CHARINDEX to pick the position of '>>'. With this I take the last '>>' of the sentence, right after the RIGHT function to return the right part of the sentence with the number of characters specified in CHARINDEX...

LTRIM() removes left spaces!

I’m not so good with explanations, anything, test EVERY FUNCTION of the example below and see how it works.

Example 1:

SELECT LTRIM(RIGHT('Faturamento >> Cupom Fiscal', CHARINDEX('>>', REVERSE('Faturamento >> Cupom Fiscal'))-1))

Example 2:

SELECT LTRIM(RIGHT('Faturamento >> Cupom Fiscal >> Farmácia Popular', CHARINDEX('>>', REVERSE('Faturamento >> Cupom Fiscal >> Farmácia Popular'))-1))

Answer 3

You can use a separation function and pick up the last part of the sequence:

SELECT (SELECT TOP(1) sep.item
          FROM separacao(s.solcaminho, ' >> ') sep
         ORDER BY sep.sequencia DESC) AS solcaminho
  FROM solicitacao s;

The code of the function used is as follows::

IF OBJECT_ID('separacao', 'TF') IS NULL
BEGIN
  EXEC('CREATE FUNCTION separacao() RETURNS @partes TABLE(t INT) AS BEGIN RETURN END');
END;
go

ALTER FUNCTION separacao(@frase       VARCHAR(MAX),
                         @delimitador VARCHAR(MAX) = ',')

RETURNS @partes TABLE (item      VARCHAR(MAX),
                       sequencia INTEGER)

BEGIN
  DECLARE @parte     VARCHAR(MAX)
  DECLARE @sequencia INTEGER

  SET @sequencia = 0;

  WHILE CHARINDEX(@delimitador, @frase, 0) <> 0
  BEGIN
    SET @parte = SUBSTRING(@frase, 1, CHARINDEX(@delimitador, @frase, 0) - 1);
    SET @frase = SUBSTRING(@frase, CHARINDEX(@delimitador, @frase, 0) + LEN(REPLACE(@delimitador, ' ', '_')), LEN(@frase));

    IF LEN(@parte) > 0
    BEGIN
      INSERT INTO @partes(item, sequencia)
      VALUES(@parte, @sequencia + 1);

      SET @sequencia = @sequencia + 1;
    END;
  END;

  IF LEN(@frase) > 0
  BEGIN
    INSERT INTO @partes(item, sequencia)
    VALUES(@frase, @sequencia + 1);
  END;

  RETURN;
END;
go

Here is the function code used and here you check the script to test the response.

Answer 4

Try it like this:

select substring('começo;meio#fim',
         charindex('#', 'começo;meio#fim') + 1, len('começo;meio#fim')
       )
  from tabela;

See working here on sqlfiddle.