Query to pick text after a certain 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" as it does not have "»"

On the second Row I need to pick up ONLY "Popular Pharmacy" because 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 I was not successful. I posted this question here some time ago but I did not obtain result.

Thank you.

Answer 1

You can get the position of the last occurrence of >> using the function LOCATE together with the REVERSE and after that use the amount of characters found in this result to return the characters to the right, using RIGHT:

SELECT CASE x.posicao
         WHEN 0 THEN x.solcaminho
         else RIGHT(x.solcaminho, x.posicao - 1)
       END AS solcaminho
  FROM (SELECT *,
               LOCATE(' >> ', REVERSE(s.solcaminho)) AS posicao
          FROM solicitacao s) x

Here you check the SQL Fiddler with the proposal working.

Answer 2

After you do the query you can do this treatment, before returning the query result to the grid or other element you are using, just grab the code and play inside a PHP file and take the test.

// aqui é o campo caminho que vai retornar da consulta.

$caminho ="Faturamento>>Cupom>>Add";
echo "Caminho a ser quebrado<br>".$caminho;
$string = explode('>>', $caminho);

echo "<br>String quebrada<br>";
echo "<pre>";// echo pre

print_r($string);
$s = (count($string))==1?'0':count($string)-1;

echo "vou imprimir so a ultima parte do caminho ".$string[$s];