CODEWITHSUNDEEP
sqlsql-servert-sqlwildcardwhere-clause
knahs
knahs

Reputation: 75

LIKE query in T-SQL

I am not getting the desired results with the following query that uses LIKE. Can I use the WHERE clause? 

UPDATE TABLE
SET ALPHA = 'ABC'
WHERE ALPHA LIKE 'ABC%' 

UPDATE TABLE
SET ALPHA = 'ABCCC' 
WHERE ALPHA LIKE ‘ABCC%'


Bt90Abc Abc
bt91Abc Abc
bt19abcCC      ABCCC
bt16abcSTAT ABC

Upvotes: 0

Views: 778

Answers (3)

Taryn
Taryn

Reputation: 247880

You can use a CASE statement to perform the update:

update yourtable 
set alpha = case 
              when alpha like '%abcc%' then 'ABCC'
              when alpha like '%abc%' then 'ABC'
              else alpha
            end
where alpha like '%abc%';

See SQL Fiddle with Demo.

This will update any rows that match the criteria in the LIKE with your values. If it does not, then the alpha value will not be updated.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

From reading your comments:

UPDATE TABLE
SET ALPHA = CASE WHEN ALPHA IN ('bt90abc','bt91abc') THEN 'ABC' 
                 WHEN ALPHA IN ('bt19abc')  THEN 'ABCCC'
            ELSE ALPHA END
WHERE ALPHA IN ('bt90abc','bt91abc','bt19abc')

Upvotes: 0

Deadron
Deadron

Reputation: 5289

From a cursory glance it appears you could have two standout issues here.

-String comparisons are case sensitive and you are searching for uppercase letters while it may contain lower case ones. Try throwing an UPPER around your column in the where clause.

-Secondly, it appears you are searching strings for ABC that do not start with ABC. In this condition you should use '%ABC%' as your where clause.

For better input you should provide more information about your problem.

Upvotes: 0

Related Questions