How to search and find a string in another table column

Transact-SQL
1

I am creating a view from two tables. tbl1 - Sales and tbl2 Shipments.

My selection is as follows:

SalesOrder, ShippedOrders , OrderDate, ShipDate, TrackingNumber, Expr1

I need to find if ShippedOrders column contains an order from SalesOrder column.

For Example: SalesOrder is equal to AB345
ShippedOrders is equal to AB345 AB344 AB367
If SalesOrder AB345 is found in ShippedOrders column I would make Expr1 equal to SalesOrder.

Any help greatly appreciated

Thanks.

2

You can use CHARINDEX to see if one string is found in another.

3

this is what I am trying to do but I am getting an error message

CASE WHEN CHARINDEX(companyB.dbo.SorMaster.SalesOrder , dbo.ats_shipment_tracking.SalesOrders) > 0 THEN companyB.dbo.SorMaster.SalesOrder ELSE NULL END

4

What's the error?
I do see that you have a four part name for the first parameter of CHARINDEX. Is that intentional?
Can you supply the table definition and your current query?

5

I tried to recreate your problem, but I'm not sure if this is what you want. Try the example below using the name of your tables

create table #SaleOrders
(
  SalesOrder varchar(10)
);

create table #shipperO
(
  shipperOrder varchar(10)
);
 
insert into #saleOrders
values 
('AC4D0'),
('AB490'),
('AC4D0');


insert into #shipperO
values
('AB345'),
('AB378'),
('AB325');

WITH Filter
AS
(
SELECT s.salesorder AS expr      
 FROM #SaleOrders AS s
 WHERE exists (SELECT * FROM #shipperO AS p WHERE s.SalesOrder=p.shipperOrder)
)
SELECT t.salesOrder, f.expr FROM #SaleOrders AS t 
left join filter AS f ON t.SalesOrder=f.expr