troubleshooting Question

Finding and matching sets of numbers in strings

Avatar of qube09
qube09 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
7 Comments1 Solution329 ViewsLast Modified:
Ideally the solution will be a join between two tables in two servers..

I have a problem that I have been unable to resolve. Basically I need to retrieve a code from a database. There are less than 500 codes in a database containing thousands of records. There are tables involved.

One,table A has a code as part of the record such as:
codeA
60671
60449
63093
63094
61198
61184
61372
66301
61906
64916
65825
63482
65869
65870
62799
63375
63922
61308
62326
65129
60405
66336
60687
63506
65547
63475

The other, table B, has records with two columns of interest. Here they are:
codeB              location
null                sitenamepl^51458
null                sitenamepl^51298
abc872390    sitenamepl^60671
abc872393    sitenamepl^60449^sitenamepl^63093^sitenamepl^63094
abc872396    sitenamepl^61198^sitenamepl^61184^sitenamepl^61372
null                sitenamepl^51198
abc873803    sitenamepl^66301^sitenamepl^61906^sitenamepl^64916^sitenamepl^65825
abc873814    sitenamepl^63482^sitenamepl^65869^sitenamepl^65870
abc878883    sitenamepl^62799^sitenamepl^63375^sitenamepl^63922
abc878888    sitenamepl^61308^sitenamepl^62326^sitenamepl^65129^sitenamepl^60405
abc882410    sitenamepl^66336
null                sitenamepl^51198
abc882414    sitenamepl^60687
abc883363    sitenamepl^63506^sitenamepl^65547^sitenamepl^63475

Code A is used to match the numbers after sitenamepl^ in column location in order to retrieve codeB. (No I did not design this.)

Code A will probably (but not neccessarily) match one of the sitenamepl^nnnnn constructs such as sitenamepl^63506. You should note that there may be more than one  sitenamepl^nnnnn entry in the column location.

CodeA char(10)
CodeB varchar(255)
Location varchar(255)

Each table is on a different sql server and to match requires a collate database default.

Finding the nnnnn after sitenamepl^nnnnn in location for the match with codeA in order to find codeB in an efficient manner is another issue since ideally the code will numerous times since the table containing codeA is updated constantly. The table containing codeB and location is a reference table and is not updated frequently.

The objective and acceptable solution is efficient sql to complete a match of codeA to the numbers following sitename^ in order to obtain codeB. Ideally this be in a join between the two tables.

Only sql code is acceptable for inclusion in a stored procedure under sql 2005 to be upgraded to sql2008.

At this time I merely use a version of fnsplit and cross apply in what amount to a two step solution by building a separate reference table for location for the match . looking for something more sophistocated.
thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros