How to use SQL function CONCAT with LIKE

Question

I confirmed that the columns in the SQL table contain the results I'm searching for (the results being NA), but when I use the query below, no results are returned. Could I get some help with this please?

SELECT DISTINCT * 
FROM [DB_NAME].[dbo].[TABLE_NAME]
WHERE BECDescription like '%Time Warner%' AND
      (CONCAT(PhysicalAddress1, PhysicalCity, PhysicalStateProvince) like ('%NA%  %NA% %NA%')
      ) AND
      PhysicalCountry like '%NA%' AND
      CarrierName like '%NA%' AND
      CurrNetChargeAmt = 1326.00

Please include some sample data for the MSAudit table which explains what you are trying to do here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 16, 2019 at 15:43

Gordon Linoff · Accepted Answer · 2019-07-16 16:03:54Z

1

I'm a little lost on why you are using CONCAT() here. Doesn't this do what you want?

WHERE BECDescription like '%Time Warner%' AND
      PhysicalAddress1 like '%NA%' AND
      PhysicalCity like '%NA%' AND
      PhysicalStateProvince like '%NA%' AND
      PhysicalCountry like '%NA%' AND
      CarrierName like '%NA%' AND
      CurrNetChargeAmt = 1326.00

It is certainly simpler to understand.

answered Jul 16, 2019 at 16:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1724708 · Accepted Answer · 2019-07-16 17:40:33Z

0

You concat PhysicalAddress1, PhysicalCity, PhysicalStateProvince but you add spaces between the '%NA'.

Try

SELECT DISTINCT * 
FROM [db_name].[dbo].[table_name]
WHERE BECDescription like '%Time Warner%' AND (CONCAT(PhysicalAddress1, ' ', PhysicalCity, ' ', PhysicalStateProvince) like ('%NA% %NA% %NA%')) AND PhysicalCountry like '%NA%' AND CarrierName like '%NA%' AND CurrNetChargeAmt = 1326.00

edited Jul 16, 2019 at 17:40

user1724708

1,4898 gold badges31 silver badges58 bronze badges

answered Jul 16, 2019 at 15:48

Jorge Ribeiro

1,1387 silver badges17 bronze badges

1 Comment

user1724708 Over a year ago

Thank you for your help and the quick response.

Collectives™ on Stack Overflow

How to use SQL function CONCAT with LIKE

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related