SQL Server Charindex Problem

Question

all right? I find myself in a dilemma regarding the use of Charindex from SQL Server. However, I need to read a table field where the address and number are only separated by a blank space. I thought to use the Charindex command to search for the white space and bring with the right() command the exact "piece" I need.

I’m using the following command:

rtrim(right('RUA GENERAL MANOEL VARGAS 253', charindex(' ','RUA GENERAL MANOEL VARGAS 253')-1))

The idea above is to read the string from right to left, searching with charindex() the first white space found, and with this result, use the right() to bring only that piece of string that interests me.

It turns out that for this situation works perfectly:

  select distinct
  rtrim(right('RUA GENERAL MANOEL VARGAS 253', charindex(' ','RUA GENERAL MANOEL VARGAS 253')-1))
  from dbo.ztemp_cadclifor;

Upshot:

253      // => perfeito

Already in this situation does not work:

  select distinct
  rtrim(right('AVENIDA SAMPAIO VIANA 277', charindex(' ','AVENIDA SAMPAIO VIANA 277')-1))
  from dbo.ztemp_cadclifor;

Upshot:

ANA 277      // => aqui eu esperava o resultado = 277

You can help me try to understand the situation?

Thank you.

Answer 1

If you consider the property number as the last element of the address, then using the REVERSE() function makes it easy to obtain only the number.

-- código #1
SELECT right (ENDERECO, (charindex (' ', reverse (ENDERECO)) -1)) as NUMERO
  from dbo.ztemp_cadclifor;

---

If you need to separate the various elements of the address, I suggest reading the article "Split a list of values”.

Answer 2

Olà, Voce tried with SUBSTRING_INDEX?

SELECT distinct SUBSTRING_INDEX('AVENIDA SAMPAIO VIANA 277',' ',-1)
FROM
  dbo.ztemp_cadclifor;