SQLTeam.com | Weblogs | Forums

How to search and find a string in another table column


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



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


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


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?


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

insert into #shipperO

WITH Filter
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