sqlsql-servert-sqlsubstringcharindex

SUBSTRING WITH CHARINDEX

I have a column containing two concatenated data separated by a '/', I want to get only what is before '/' that is equal to a column of another table, so I am trying to use SUBSTRING with CHARINDEX, but I think I'm doing something wrong, follow the code.

SELECT TOP 1 * FROM arquivo A, tabela_geral B (NOLOCK), campo_concatenado C (NOLOCK)
WHERE A.primeira_parte = SUBSTRING(C.concatenado, 1, CHARINDEX('/', C.concatenado) -1)
AND B.status = 0
AND B.campo = '13'
AND B.numero NOT IN (6, 78, 79, 80, 81, 82, 83, 91)
Solution

  • You should handle rows which don't have '/'

    If you consider the values like before '/'

    SELECT TOP 1 * FROM arquivo A, tabela_geral B (NOLOCK), campo_concatenado C (NOLOCK)
WHERE A.primeira_parte = COALESCE(SUBSTRING(C.concatenado, 1, NULLIF(CHARINDEX('/', C.concatenado), 0) -1), C.concatenado)
AND B.status = 0
AND B.campo = '13'
AND B.numero NOT IN (6, 78, 79, 80, 81, 82, 83, 91)

    If you consider the values like after '/'

    SELECT TOP 1 * FROM arquivo A, tabela_geral B (NOLOCK), campo_concatenado C (NOLOCK)
WHERE A.primeira_parte = SUBSTRING(C.concatenado, 1, NULLIF(CHARINDEX('/', C.concatenado), 0) -1)
AND B.status = 0
AND B.campo = '13'
AND B.numero NOT IN (6, 78, 79, 80, 81, 82, 83, 91)

    And I strongly recommend you to pay attention on Sean Lange comments