INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Search String mapping help

Search String mapping help

(OP)
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

CODE

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.

CODE

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,

RE: Search String mapping help

Am I missing something here...?

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

CODE

Terminal_Table
Terminal_ID     Name                Company_ID
1               PAYPALxxxJETSTAR    NULL	
2               BLAxBLA             NULL 

and you do:
SELECT * FROM TERM_TABLE
WHERE NAME LIKE ANY ('%PAYPAL%','%PAYPAL%ATM%')

you get:

CODE

Terminal_Table
Terminal_ID     Name                Company_ID
1               PAYPALxxxJETSTAR    NULL 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Search String mapping help

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 '%PAYPAL%ATM%' and '%JETSTAR%AIR%' search values extraneous as they would both be covered by the first search values for each company?

RE: Search String mapping help

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

CODE

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close