September 25, 2014 at 11:56 am
I am trying to Join Table P with Table T based on the values before 1st two "."
declare @P table
(
Name_Section varchar(200)
)
insert into @P values
('10.158.1.1'),
('10.548.9.9'),
('10.152.222.1')
Select * from @P
So , if it is 10.159.1.1
Then it should take the values until 10.159 and compare with table T .
Table T has values in a row after ; too
declare @T table
(
Name_Section varchar(200)
)
insert into @T values
('10.1.5.0;10.105.6.0;10.55.0.0'),
('10.548.1.1'),
('10.159.0.0;10.152.222.1;99.989.00.00')
Select * from @T
Can anyone suggest how can I join these ...
September 25, 2014 at 12:07 pm
Quick solution, not perfect but it works most of the time
😎
SELECT
P.Name_Section
,T.Name_Section AS Matched_row
FROM @T T
OUTER APPLY @P P
WHERE CHARINDEX(';' + P.Name_Section,';' + T.Name_Section + ';',1) > 0;
Results
Name_Section Matched_row
-------------- -------------------------------------
10.152.222.1 10.159.0.0;10.152.222.1;99.989.00.00
September 25, 2014 at 12:18 pm
As Eirikur stated his solution will work for some cases but certainly not all.
Add one more row to @T ,('10.158.4.5'). That will be missed in his solution.
You first have to parse the delimited list of IP addresses. Then joining on only the first two octets is a little strange but something like this might be a bit closer.
with ParsedValues as
(
select *
from @T t
cross apply dbo.DelimitedSplit8K(Name_Section, ';')
)
select *
from ParsedValues pv
join @P p on parsename(p.Name_Section, 4) + '.' + parsename(p.Name_Section, 3) = parsename(pv.Item, 4) + '.' + parsename(pv.Item, 3)
This is using the Jeff Moden / community splitter found by following the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2014 at 12:25 pm
Wondering why are we using dbo.DelimitedSplit8K?
September 25, 2014 at 12:28 pm
sharonsql2013 (9/25/2014)
Wondering why are we using dbo.DelimitedSplit8K?
Because you have a delimited list of IPs in @T. If you want to join on the first two octets of each IP in there you have to first parse the delimited values into something usable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2014 at 12:50 pm
Can I use a temp table instead of @DelimitedSplit8K?
September 25, 2014 at 1:04 pm
sharonsql2013 (9/25/2014)
Can I use a temp table instead of @DelimitedSplit8K?
Not really, you can load the results from the DelimitedSplit8K function into a temp table, can be beneficial, especially if you add an index to the temp table.
😎
September 25, 2014 at 1:05 pm
sharonsql2013 (9/25/2014)
Can I use a temp table instead of @DelimitedSplit8K?
That is sort of like asking if you can use a pumpkin instead of a tuna fish. They are nothing even similar. You are going to need to split the denormalized list into rows before you can do much with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply