SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 (Sub)string compare from two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Neven1986
Starting Member

6 Posts

Posted - 01/23/2013 :  18:51:39  Show Profile  Reply with Quote
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

3585 Posts

Posted - 01/23/2013 :  19:14:24  Show Profile  Reply with Quote
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;
Go to Top of Page

Neven1986
Starting Member

6 Posts

Posted - 01/26/2013 :  18:32:26  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2013 :  14:02:56  Show Profile  Reply with Quote

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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/27/2013 :  14:07:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 01/27/2013 :  16:54:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000