Avatar of qube09
qube09
 asked on

Finding and matching sets of numbers in strings

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!
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
vasto

8/22/2022 - Mon
vasto

In order to get both tables together you can use linked servers.

SELECT ..... FROM Table1 a INNER JOIN Table2 b ON b.location +'^' LIKE '%^' + a.CodeA + '^%'
qube09

ASKER
wouldn't it be more efficient to avoid the use of +'^' LIKE '%^' + a.CodeA + '^%'
vasto

I agree that it is not efficient to join on strings , but you don't have choise.
CHARINDEX will be probably more efficient, but you still need to use similar way:

SELECT ..... FROM Table1 a INNER JOIN Table2 b ON CHARINDEX('^' + a.CodeA + '^' , b.location +'^' ) > 0
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Anthony Perkins

wouldn't it be more efficient to avoid the use of +'^' LIKE '%^' + a.CodeA + '^%'
Absolutely.  But by stating "Only sql code is acceptable" you have just tied both our hands behind out back and now you expect us to dance a tango.

SQL Server (T-SQL) is lousy at string manipulation, but if that is all you can use than you have no choice.
qube09

ASKER
It has to be sql because that is exactly what I have to use and more to the point maintain. And that is why I am using a version of fnsplit which is working adequately. I had hoped for something better.
Anthony Perkins

And that is why I am using a version of fnsplit which is working adequately. I had hoped for
something better.

Unless you can use CLR, then using T-SQL string functions is all you can do.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
vasto

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question