CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringcharindex
Gabriel Sarzi
Gabriel Sarzi

Reputation: 23

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)

Upvotes: 0

Views: 806

Answers (2)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

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

Upvotes: 1

user9051526
user9051526

Reputation:

Did you tried this ? 

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

Upvotes: 1

Related Questions