SQLTeam.com | Weblogs | Forums

How to check if a field contains two strings?


#1

For example,in my Table1, columnA which holds customer name I need to filter if the string has two words, if yes I need to update columnC to 1. How do I search if a field contains two different strings?

Thanks


#2

How do you define "has two words"?

  1. If value includes a space: WHERE customer_name LIKE '% %'
  2. has a space OR some type of punctuation: WHERE customer_name LIKE '%[ ,;-/]%'

etc.


#3

If you are looking for the presence of two specific strings (rather than whether there are two words, regardless of what those words are)

UPDATE Table1 SET
    ColumnC = 1
WHERE
   ColumnA LIKE '%string1%'     -- string1 is the first string you are looking for
   AND ColumnA LIKE '%string2%' -- string 2 is the second string you are looking for

#4

I am looking for any words(strings) need to make sure that there are 2.. I am not looking for a particular string. First I need to isolate records that have 2 strings could be anything. If yes I would need to update another column in this table.


#5

Again, specifically what do you mean when you say "there are 2"?

For now, I'll assume you mean that a space exists in the column:

UPDATE t1
SET columnC = 1
FROM dbo.table1 t1
WHERE t1.columnA LIKE '% %'


#6

2 means two words , different the same does not matter just looking for 2 strings.


#7

SPECIFICALLY how do YOU define "2 words"?

Which, if any, of these strings contain "2 words"?

ABC DEF
ABC/DEF
ABC.DEF
ABC;DEF
ABC@mail1.com
ABC--QRL
123,456
12E+22

Keep in mind, we know NOTHING about your data.


#8

Two words would in my case
ABC DEF or 123,456


#9
UPDATE t1
SET columnC = 1
FROM dbo.table1 t1
WHERE t1.columnA LIKE '%[ ,]%'

#10

Well this displays everything with 2 or more words for example
JAGGED STORE (EDMONTON)
ANA MARIA PARLMER
ABE DRIED / TIANA DRIED
ANN CARMEN - UPDATE
ANN, CARMEN - UPDATE

My columnC needs to be updated only if example last last two.


#11

If you say so. I have NO idea what criteria you used to decide which value(s) had "two words", and you refuse to state it, so I will drop out and allow someone else who may understand it better than I do to help you.


#12

If this gives you the results you expect:

select *
  from table1
 where columna like '%[ ,]%'
   and columna not like '%[ ,]%[ ,]%'
;

then do the update like this:

update table1
   set columnc=1
 where columna like '%[ ,]%'
   and columna not like '%[ ,]%[ ,]%'
;