CASE With Wild Card Search

[SA812]

Hello,
I'm trying to create a CASe statement that finds PO BOX records in an address field. If i find a text containing %P%O%BOX% i want it to return the data else leave it blank. Here is what i have written so far but i do not get any results.

CASE WHEN tciAddress.AddrLine1 = '%BOX%' THEN tciAddress.AddrLine1 ELSE '' END

Thanks in advance for your help.

 

[gmmastros]

How about....

CASE WHEN tciAddress.AddrLine1 Like '%P%O%BOX%' 
     THEN tciAddress.AddrLine1
     ELSE '' END

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

What if the address is something like:
"POrtsmouth, BOXsomething,..."
Or even you have P, O and BOX in 3 different words
Maybe you could search for whole phrase:

' '+tciAddress.AddrLine1+' ' Like '% PO BOX %'

Again. This is NOT the perfect way :-(

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SA812]

God i love this web site! Thank you guys.

One follow up though. If i wanted to exclude '%P%O%BOX% from street address? I tried below but it didn't exclude the PO Box records.

CASE WHEN tciAddress.AddrLine1 NOT Like '%P%O%BOX% 'THEN tciAddress.AddrLine1 ELSE '' END

I also tried this but it excludes everything
LEFT (CASE WHEN tciAddress.AddrLine1 LIKE '[^P%O%BOX] ' THEN tciAddress.AddrLine1 ELSE '' END, 35)