Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 (Sub)string compare from two tables

Author  Topic 

Neven1986
Starting Member

6 Posts

Posted - 2013-01-23 : 18:51:39
Hello,

i was wondering if someone could give me advice on my problem.

I have 2 tables.
One called PrefixList and one called CustomerHistory.

PrefixList

ID | Prefix(varchar)
---------------
1 | 3851
2 | 385
|


CustomerHistory

CustID | OrderNo (varchar)
------------------------
1 | 38512345
2 | 38554321
|


What I want to do is to compare Prefix column from PrefixList table, with the begining of string in OrderNo column from CustomerHistory table. But that should be best match. For example: OrderNo 38512345 should be compared with Prefix 3851 beacuse first 4 chars are identical, rather than with Prefix 385 which have just 3 identical chars.

My main concern is how to do this: I could iterate through both tables and compare each row from one table with each row from second table, but I think this could be overkill for DB. Specially because CustomerHistory could have 500k rows and PrefixList could have 100 rows. Processing 500000 * 100 rows can have huge impact on DB.

Should I use temp tables, table variables or maybe cursors I'm little lost here, do you have any suggestions?

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 19:14:24
[code]SELECT
ch.CustID,
ch.OrderNo,
pl.Prefix
FROM
CustomerHistory ch
OUTER APPLY
(
SELECT TOP (1) pl.Prefix
FROM PrefixList pl
WHERE ch.OrderNo LIKE pl.Prefix+'%'
ORDER BY LEN(Prefix) DESC
) pl;[/code]
Go to Top of Page

Neven1986
Starting Member

6 Posts

Posted - 2013-01-26 : 18:32:26
quote:
Originally posted by James K

SELECT
ch.CustID,
ch.OrderNo,
pl.Prefix
FROM
CustomerHistory ch
OUTER APPLY
(
SELECT TOP (1) pl.Prefix
FROM PrefixList pl
WHERE ch.OrderNo LIKE pl.Prefix+'%'
ORDER BY LEN(Prefix) DESC
) pl;




James thank you very much for your answer, it didn't work for me as I expected. But it gave me idea in which direction should I go.
As someone wise said: I didn't saw the forest, because of trees.

Sorry for late response.

The solution i found working is:

SELECT
ch.CustID,
ch.OrderNo,
pl.Prefix
FROM
CustomerHistory ch
OUTER APPLY
(
SELECT TOP (1) pl.Prefix
FROM PrefixList pl
WHERE SUBSTRING(ch.OrderNo,1,LEN(pl.Prefix)) = pl.Prefix
ORDER BY LEN(Prefix) DESC
) pl;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-27 : 14:02:56
[code]
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ch.OrderNo ORDER BY LEN(pl.Prefix) DESC) AS Seq
FROM CustomerHistory ch
INNER JOIN PrefixList pl
ON ch.OrderNo LIKE pl.Prefix+'%'
)t
WHERE seq=1
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-27 : 14:07:06
why LIKE didnt work for you? its equivalent to logic you've written using substring.

whats the datatype of OrderNo and Prefix fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 16:54:26
Just seconding what Visakh said - logically the substring query and the like query should be equivalent, if the data type of your columns are VARCHAR(nn) or NVARCHAR(nn) The reason LIKE clause would be better than the substring is that, with the like clause SQL Server can potentially use an index on the OrderNo column if there is one, but it cannot with the substring method.
Go to Top of Page
   

- Advertisement -