Search String mapping help

[peac3]

Hi all,

I am trying to assign the terminals to the company by searching from the terminal name using search string in the company table.
The input from 2 tables: company table and terminal table

Company_Table
Company_ID 	Name		Search_String
1			PAYPAL		'%PAYPAL%','%PAYPAL%ATM%'
2			JETSTAR		'%JETSTAR%','%JETSTAR%AIR'

Terminal_Table
Terminal_ID 	Name				Company_ID
1				PAYPALxxxJETSTAR	NULL	
2				BLAxBLA				NULL

I am using query builder based on the company input table to assign the company.

SELECT * FROM TERM_TABLE
WHERE NAME LIKE ANY ('%PAYPAL%','%PAYPAL%ATM%')

The problem is after running the search, the terminal can have multiple companies as per current output below.

Terminal_ID 	Name				Company_ID
1				PAYPALxxxJETSTAR	1	
1				PAYPALxxxJETSTAR	2
2				BLAxBLA				NULL

In this case, I would like the solution to find the first search string first (using CHARINDEX or something) then assign to the company ID
eg. PAYPALxxxJETSTAR assign to PAYPAL not JETSTAR

Appreciate on you guys input on this.

Thanks Guys,

 

[Andrzejek]

Am I missing something here...?

If this is your Terminal_Table (is it the same as TERM_TABLE ?)

Terminal_Table
Terminal_ID     Name                Company_ID
1               PAYPALxxxJETSTAR    NULL	
2               BLAxBLA             NULL

and you do:[tt]
SELECT * FROM TERM_TABLE
WHERE NAME LIKE ANY ('%PAYPAL%','%PAYPAL%ATM%')[/tt]
you get:

Terminal_Table
Terminal_ID     Name                Company_ID
1               PAYPALxxxJETSTAR    NULL

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[DaveInIowa]

Is LIKE ANY a new feature of SQL Server? I'm not aware this is valid SQL. If it is, that would be a nice feature.

Also, aren't the [tt]'%PAYPAL%ATM%'[/tt] and [tt]'%JETSTAR%AIR%'[/tt] search values extraneous as they would both be covered by the first search values for each company?

 

[DaveInIowa]

If you could get your company search string values split and normalized like in the @CompanySearch table shown below, this may work for you...

DECLARE @CompanySearch TABLE (Company_ID INT, SearchValue VARCHAR(32))
INSERT INTO @CompanySearch VALUES (1, '%PAYPAL%'), (2, '%JETSTAR%'), (1, '%PAYPAL%ATM'), (2, '%JETSTAR%AIR')

DECLARE @Terminal_Table TABLE (Terminal_ID INT, Name VARCHAR(32), Company_ID INT)
INSERT INTO @Terminal_Table (Terminal_ID, Name) VALUES (1, 'PAYPALxxxJETSTAR'), (2, 'BLAxBLA')

;WITH PotentialCompanies AS (
SELECT t.Terminal_ID, cs.Company_ID, PATINDEX(cs.SearchValue, t.Name) PatIndex
  FROM @Terminal_Table t
 CROSS
  JOIN @CompanySearch cs
 WHERE PATINDEX(cs.SearchValue, t.Name) > 0
)

UPDATE t
   SET Company_ID = cm.PrimaryCompany_ID
  FROM @Terminal_Table t
 CROSS
 APPLY (SELECT TOP 1 Company_ID PrimaryCompany_ID
		  FROM PotentialCompanies
		 WHERE Terminal_ID = t.Terminal_ID
		 ORDER BY PatIndex) cm

SELECT *
  FROM @Terminal_Table