June 7, 2012 at 7:51 pm
Hello,
I'm using sql server 2008 SP1.
I have 2 tables
Table 1 has 2 columns Name, Number
Name Number
X1 12345
X2 145896
X3 145896
X4 56238
Table 2 has 1 column
Number
12345a2
56238b2
56238c5
145896z3
I would like to know how to I can write a script to extract the column Name in Table 1 when Number in Table 2 is like or partially matches Number in Table 1.
for example, Table 1 has 12345 and table 2 has 12345a2, I want to extract the Name X1 as Number in Table 2 data partially matches Number in Table 1.
Thanks for your help in advance,
JM
June 7, 2012 at 8:20 pm
You didn't say what to do with duplicates, so this solution returns them:
DECLARE @t1 TABLE (Name VARCHAR(2), Number VARCHAR(10))
DECLARE @t2 TABLE (Number VARCHAR(10))
INSERT INTO @t1
SELECT 'X1','12345' UNION ALL SELECT 'X2','145896'
UNION ALL SELECT 'X3','145896' UNION ALL SELECT 'X4','56238'
INSERT INTO @t2
SELECT '12345a2' UNION ALL SELECT '56238b2'
UNION ALL SELECT '56238c5' UNION ALL SELECT '145896z3'
SELECT Name, t1.Number, t2.Number
FROM @t1 t1
INNER JOIN @t2 t2 ON t2.Number LIKE '%' + t1.Number + '%'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 7, 2012 at 10:58 pm
Thank you very much. It works just PERFECT....
June 7, 2012 at 11:03 pm
You're welcome.
Be aware that you may want to consider dropping the first '%' if you don't need to consider any prefix for the number.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply