Reputation: 1591

Alternative for 'CharIndex' in SQL

I've a sql table in which one of the column 'Codes' consists of multiple character values sperated by '|'.

(For ex.:|0000000171|0000001192|0000003498|0000003569|).

Now in a query inorder to check a single value if it exists in the colum 'Codes', One row is succesfully returned if I use:-

charindex('0000000171', Codes) > 0

Question:- What could be the correct query in SQL if I want to search more than one value, if any of them exist in column 'Codes'.

For ex. If need to search the existence of three values 0000000171, 0000000172, 0000000173 in column 'Codes', following syntax return Nothing:-

charindex('0000000171,0000000172,0000000173', Codes) > 0

Please advice ...

Upvotes: 1

Answers (3)

Reputation: 1270713

If you need to use a delimited list, then your charindex should include the delimited on each code, as in:

charindex('|'+'0000000171'+'|', Codes) > 0

charindex('|0000000171|', Codes) > 0

Whoever designed the "Codes" field seemed to have this in mind, since it begins and ends with a delimiter.

However, the right solution is to store the codes in a separate table.

Upvotes: 3

Reputation: 138990

You can use charindex three times

charindex('0000000171', Codes) > 0 or
charindex('0000000172', Codes) > 0 or
charindex('0000000173', Codes) > 0

Upvotes: 1

Reputation: 66727

What about:

(charindex('0000000171', Codes) > 0
or
charindex('0000000172', Codes) > 0
or
charindex('0000000173', Codes) > 0)

Upvotes: 1