Link to home
Start Free TrialLog in
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!
Avatar of vasto
vasto
Flag of United States of America image

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 + '^%'
Avatar of qube09
qube09

ASKER

wouldn't it be more efficient to avoid the use of +'^' LIKE '%^' + a.CodeA + '^%'
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
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.
Avatar of 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.
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.
ASKER CERTIFIED SOLUTION
Avatar of vasto
vasto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial