How to extract a word from a string (SQL Server)

Question

I’m having a problem with my script.

Context: I need to get information from a string that is in the nm_content column, but all the functions I try are not working. I tried to do it separately and it only works with a row, when I add a column, it gives error. Follow the command I tried

select top(1000) nm_retorno as teste
CASE WHEN nm_retorno LIKE '% id_revenda %'
                THEN SUBSTRING(nm_retorno, (len(nm_retorno) - CHARINDEX(REVERSE('id_revenda'), REVERSE(nm_retorno)) + 12), len(nm_retorno))
                ELSE nm_retorno
           END as TesteRevenda
from Tabela

I tried some other ways but was unsuccessful. The error that appears is

Message 156, Level 15, Status 1, Line 2 Incorrect syntax near the keyword 'CASE'.

I also tried the following script and gave error

select top(1000) CASE WHEN nm_retorno LIKE '% ID_REVENDA=" %'
                THEN SUBSTRING(nm_retorno, (len(nm_retorno) - CHARINDEX(REVERSE('ID_REVENDA="'), REVERSE(nm_retorno)) + 12), len(nm_retorno))
                ELSE nm_retorno
           END as TesteRevenda
from Tabela

Returns the following error:

Message 8116, Level 16, Status 1, Line 1 Argument data type text is invalid for argument 1 of Len Function.

Grateful from now on!

Answer 1

Beyond the missing comma.

To get the size of the text in a column of type text you need to use the function DATALENGTH(<nome_da_coluna_tipo_texto>)

That’s why it’s important you share your bank script... So we can see how the table was built.

This error below happens because you are using a column of the type text that the function len() can’t stand it.

Message 8116, Level 16, Status 1, Line 1 Argument data type text is invalid for argument 1 of Len Function. - Lucas Rossini 10 hours ago

So you need to use DATALENGTH() that solves:

The function DATALENGTH() returns the number of bytes so you need to divide the value by 2 to get the character size;

SELECT top 10
    AddressLine1 AS Rua,
    DATALENGTH(AddressLine1)/2 AS TamanhoTexto,
    CASE WHEN AddressLine1 LIKE '% Napa %'
         THEN SUBSTRING(AddressLine1, CHARINDEX('Napa', AddressLine1), len('Napa') )
         ELSE 'Não tem'
    END as ResultadoCase
FROM Person.Address
order by AddressID;