CODEWITHSUNDEEP
sqlsql-server-2008
viramgami satish
viramgami satish

Reputation: 29

How do I search for a string from table column in SQL Server using CHARINDEX function

I have a string in table column like below

1. "C-DERD,C-FDER,E-FDFE,E-DFE,C-DFERE"
2. "E-FDFE,C-DFEC,E-DFDFE,E-DE"

If I search for a string like "DE" then it will first check all the individual string that starts with 'C-' AS LIKE CONSTRAINT IN TABLE. If it is found, then it will return TRUE in separate column, in second case it is not found in 'C-' case then it will check in all individual string that start with 'E-' but it is same as searched string. With use of CHARINDEX function only.

Upvotes: 0

Views: 476

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

As I mention in a comment, you should fix the data structure. Sometimes, though, we are stuck with other people's really bad designs and are not in a position to fix them.

One method is to split the string, although that is a bit painful. In your case you could do:

select t.*,
       (case when str like '%CE-DE%' then 1
             when str like '%CE-[^,]DE%' then 1
             when str like '%CE-[^,][^,]DE%' then 1
             when str like '%CE-[^,][^,][^,]DE%' then 1
             else 0
        end) as flag

This looks for up to three intervening characters between the - and DE, which is sufficient for your examples.

Upvotes: 2

Related Questions