CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Arnab
Arnab

Reputation: 2354

using case and contains in SQL

I have a column x-property which has values xxx-abc, xxx-def, 123, mno ....etc.

I have another column isx.

I wish to update table to fill up column isx such that if the row in x-property column contains xxx then add abc, else add xyz.

I do not have SQL full text search in my table.

Any help is appreciated.

Upvotes: 0

Views: 9192

Answers (1)

timo.rieber
timo.rieber

Reputation: 3867

Instead of contains use like, because from the docs:

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

This is your query:

update
    table
set
    isx = 
        case
            when x-property like '%xxx%' then 'abc'
            else 'xyz'
        end

Upvotes: 2

Related Questions