May 17, 2010 at 2:36 pm
HI,
I got two tables
Table1:
Col1 col2 col3 col4 col5
Table2:
col6 col7 col8
now i need to perform a join on both the tables based on col3 in table1 and col7 in table2
Now contents of col3 in tbale1 are like
col3
-----
c:\abcd\kljfhd\jfj\ifjdf\\id
d:\dffn\eijfcn\caslk\mslw
e:\lsamd\dmwp\ nsdkd
\\abc.gh.fh.k\efl\i\d
\\dfe\hki\hy\id
.
.
.
.
contents of col7 in table2
col7
-------
dskjl\\ab\do\i\get
lkl\\ksjk\kflsk\mds\dcm\dn
[asf]\\jcsdc\mcd\dnd\dvn\
[xxxx]\\abc\efl\i\l
[dsjf]\\dfe\hki\hy\lk
.
.
.
.
.
Join operation based on:
the highlighted part of string in each row of col3 of table1 should equal to the highlighted part of string in the rows of col7 in table2
and for example if you take one row for each colum
col3 col7
-------------- ----------------
\\abc.gh.fh.k\efl\i\d [xxxx]\\abc\efl\i\l
now the highlighted part in col3 is \\abc.gh.fh.k\efl\i
and the highlighted part in col7 is \\abc\efl\i
if you closely absorve the highlighted part in col3 if you remove the string part from . to end of the dot that is '.gh.fh.k' col3 will become \\abc\efl\i.
So i need help with query wich performs the join of both the tables based on the above requirements
and returns all the rows from col3 in table1 and all the rows from col7 in table2 which meet the above requirements.
i too wrote a query which is taking too much time to get the results.
May 17, 2010 at 4:50 pm
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 19, 2010 at 9:22 pm
You are trying to join on characters in the middle of a string. There is no way for an index to help with a join that vague. It's going to have to do one or more table scans. Sorry.
The way I would approach this would be to write the code to parse out the data you want to match on into separate worktables that contains the keys to the original rows in your source tables. Then index and join your worktables.
Indexes can be built over individual words with full-text indexing, and XML can be indexed, but the strings you are showing don't fit into either of those categories. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 20, 2010 at 1:52 am
This should give you somewhere to start:
DECLARE @Table1
TABLE (
col3 VARCHAR(50) NOT NULL,
other_data VARCHAR(10) NOT NULL
);
DECLARE @Table2
TABLE (
col7 VARCHAR(50) NOT NULL,
other_data VARCHAR(10) NOT NULL
);
INSERT @Table1 (col3, other_data) VALUES ('c:\abcd\kljfhd\jfj\ifjdf\\id', 'row T1-1');
INSERT @Table1 (col3, other_data) VALUES ('d:\dffn\eijfcn\caslk\mslw', 'row T1-2');
INSERT @Table1 (col3, other_data) VALUES ('e:\lsamd\dmwp\ nsdkd', 'row T1-3');
INSERT @Table1 (col3, other_data) VALUES ('\\abc.gh.fh.k\efl\i\d', 'row T1-4');
INSERT @Table1 (col3, other_data) VALUES ('\\dfe\hki\hy\id', 'row T1-5');
INSERT @Table2 (col7, other_data) VALUES ('dskjl\\ab\do\i\get', 'row T2-1');
INSERT @Table2 (col7, other_data) VALUES ('lkl\\ksjk\kflsk\mds\dcm\dn', 'row T2-2');
INSERT @Table2 (col7, other_data) VALUES ('[asf]\\jcsdc\mcd\dnd\dvn\', 'row T2-3');
INSERT @Table2 (col7, other_data) VALUES ('[xxxx]\\abc\efl\i\l', 'row T2-4');
INSERT @Table2 (col7, other_data) VALUES ('[dsjf]\\dfe\hki\hy\lk', 'row T2-5');
WITH Table1
AS (
SELECT Result.string,
T1.other_data
FROM @Table1 T1
CROSS
APPLY (
SELECT LEN(T1.col3) - CHARINDEX('\', REVERSE(T1.col3)),
CHARINDEX('.', T1.col3),
CHARINDEX('\', T1.col3, CHARINDEX('.', T1.col3))
) Positions (last_slash, first_dot, first_slash_after_dot)
CROSS
APPLY (
SELECT STUFF
(
CASE
WHEN Positions.first_dot = 0 THEN LEFT(T1.col3, Positions.last_slash)
ELSE STUFF(LEFT(T1.col3, Positions.last_slash), Positions.first_dot, Positions.first_slash_after_dot - Positions.first_dot, SPACE(0))
END,
1, 2,
SPACE(0)
)
) Result (string)
WHERE col3 LIKE '\\%'
),
Table2
AS (
SELECT Result.string,
T2.other_data
FROM @Table2 T2
CROSS
APPLY (
SELECT CHARINDEX('\\', T2.col7) + 2,
LEN(T2.col7) - CHARINDEX('\', REVERSE(T2.col7))
) Positions (after_double_slash, last_slash)
CROSS
APPLY (
SELECT SUBSTRING(T2.col7, Positions.after_double_slash, Positions.last_slash - Positions.after_double_slash + 1)
) Result (string)
WHERE col7 LIKE '%\\%'
)
SELECT *
FROM Table1 T1
JOIN Table2 T2
ON T2.string = T1.string;
-- Equivalent expressions:
-- [Expr1006] = Scalar Operator(CASE WHEN charindex('.',@Table1.[col3] as [T1].[col3])=(0) THEN substring(@Table1.[col3] as [T1].[col3],(1),len(@Table1.[col3] as [T1].[col3])-charindex('\',reverse(@Table1.[col3] as [T1].[col3]))) ELSE stuff(substring(@Table1.[col3] as [T1].[col3],(1),len(@Table1.[col3] as [T1].[col3])-charindex('\',reverse(@Table1.[col3] as [T1].[col3]))),charindex('.',@Table1.[col3] as [T1].[col3]),charindex('\',@Table1.[col3] as [T1].[col3],charindex('.',@Table1.[col3] as [T1].[col3]))-charindex('.',@Table1.[col3] as [T1].[col3]),'') END)
-- [Expr1005] = Scalar Operator(substring(@Table2.[col7] as [T2].[col7],charindex('\\',@Table2.[col7] as [T2].[col7])+(2),((len(@Table2.[col7] as [T2].[col7])-charindex('\',reverse(@Table2.[col7] as [T2].[col7])))-(charindex('\\',@Table2.[col7] as [T2].[col7])+(2)))+(1)))
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply