May 19, 2010 at 11:26 am
Hi i got two tables table1 and table2
like
TABLE1
create table1
(
source varchar(500)
)
insert into table1(source)
values('c:\abcd\sddf\dff\dff\d\df')
insert into table1(source)
values('d:\sf\df\eef\cvf\')
insert into table1(source)
values('\\abc.id.do.k\efl\i\d\re')
insert into table1(source)
values('\\cd\asd\fgc\dfr\g')
Now TABLE2
create table2
(
loc varchar(500)
)
insert into table2(loc)
values([sdff]\\dff\dsff\dfd\sdd)
insert into table2(loc)
values('[asco]\\dg\jk\ok\olf\')
insert into table2(loc)
values('[ddgf]\\abc\efl\i\lk\ki')
insert into table2(loc)
values('[dsfvdf]\\cd\asd\fgc\th\lkfj')
Now the two tables will be like
Table1
source
--------
c:\abcd\sddf\dff\dff\d\df
d:\sf\df\eef\cvf\
\\abc.id.do.k\efl\i[/b]\d\re
\\cd\asd\fgc\dfr\g
Table2
loc
----
[sdff]\\dff\dsff\dfd\sdd
[asco]\\dg\jk\ok\olf\
[ddgf]\\abc\efl\i\lk\ki
[dsfvdf]\\cd\asd\fgc\th\lkfj
Now i need to join the two tables with the condition that the part of string from first \ to the end of fourth \
for example take fourth row of column source in table 1
\\cd\asd\fgc\dfr\g so i need the highlighted part that is from first'\' to end of fourth '\'
and that is to be compared with the rows in table2 as of above like from the first '\' to end of fourth '\'
so in brief
table1 table2
\\cd\asd\fgc\dfr\g [dsfvdf]\\cd\asd\fgc\th\lkfj'
so when u closely look at the above values the highlighted part of the string in table1 is equal to the highlighted part of string in table2 , so that rows should be returned with inner join
And one more condition when you look at row3 in table1 i got it like
\\abc.id.do.k\efl\i\d\re
here i need to delete the part of string from first '.' to end of last '.'
so when i trim that part it will be like \\abc\efl\i\d\re .So now i can perform the compare operation from first '\' to the end of fourth '\' with the row in table 2.
i got nearly 2million records like that in table1 and table2 so i jus took some of them
May 19, 2010 at 2:37 pm
That looks like a really ugly bit of comparison. You most certainly are going to have a difficult time joining that data with a decent level of performance. You're going to need to use some sort of functions to strip out the data so that you can compare them to one another.
I'd suggest that you either use Computed Columns and use functions to strip out the data that you want to compare (and index it) or pull out the substrings that you want and dump them to another table.
Your best option is likely to use the CHARINDEX function and to nest it since you want to go from the 1st to the 4th '/'. Also use Substring function as well
See the following code for a sample of how to strip out the data:
declare @testdata varchar(20)
set @testdata = '/abcd/ef/ghi/jkl/mno'
selectcharindex('/',@testdata) PositionFirstSlash,
charindex('/',@testdata,charindex('/',@testdata) + 1) PositionSecondSlash,
charindex('/',@testdata,charindex('/',@testdata,charindex('/',@testdata) + 1) + 1) PositionThirdSlash,
charindex('/',@testdata,charindex('/',@testdata,charindex('/',@testdata,charindex('/',@testdata) + 1) + 1) + 1) PositionFourthSlash,
substring(@testdata,
charindex('/',@testdata), -- First Slash
charindex('/',@testdata,charindex('/',@testdata,charindex('/',@testdata,charindex('/',@testdata) + 1) + 1) + 1) -- Fourth Slash
) StringData
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply