| Author |
 Topic  |
|
|
Neven1986
Starting Member
6 Posts |
 Posted - 01/23/2013 : 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
Flowing Fount of Yak Knowledge
1518 Posts |
Posted - 01/23/2013 : 19:14:24
|
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; |
 |
|
|
Neven1986
Starting Member
6 Posts |
Posted - 01/26/2013 : 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; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/27/2013 : 14:02:56
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/27/2013 : 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/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1518 Posts |
Posted - 01/27/2013 : 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. |
|
|
| |
Topic |
|
(Sub)string compare from two tables
