Click here to Skip to main content
14,361,043 members


Rate this:
4.80 (4 votes)
Please Sign up or sign in to vote.
4.80 (4 votes)
23 Mar 2010CPOL
Here is a good use for SQL OUTER APPLY

This week, I have a lot of good challenges in SQL as we are building an SSIS package to unify data from different systems like Solomon, Raisers Edge, Goldmine, etc. A part of that SSIS Package is to cleanse the data and the toughest to cleanse is the addresses (which means putting the correct data on the correct fields like Post Code and Suburbs). Now I was quite challenged in one scenario where I need to extract Suburb field - I need to get that data form a consolidated address field (everything in there) and I want to do it in one Select statement!

So what are my components? I have a table of correct suburbs and a table of unclean addresses.

i.e. Suburb Table looks like this:

Suburb Table

and Address table looks like this:

Address Table

So how would I extract the correct suburb in one Select? Initially, what I was doing was:

SELECT a.CompleteAddress, s.Suburb FROM tblCrap_Addresses a
INNER JOIN tblLookup_Suburb s ON a.CompleteAddress LIKE
(SELECT TOP 1 '%' +  s.Suburb + '%' )

But there’s a problem with this one as if I have an address like 999 X Street, Mount Albert and in my suburbs I have Mount, Mount Al, and Mount Albert I will definitely get a wrong join on the suburb and will get the Mount as the suburb instead of Mt Albert. I tried a lot of stuff like indexing the Suburb table to the length of Mt Albert so it gets the longest length DESC so it joins on the longest string first but still does not work. It's also impossible to put a condition on the LIKE while doing a JOIN. Then I suddenly remembered the APPLY operator.

So what is an APPLY operator? According to Technet:

“The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.”

So my new command now is:

tblCrap_Address a
(SELECT TOP 1 s2.Suburb  FROM tblLookup_Suburb s2 where
LIKE '%' + s2.Suburb + '%' ORDER BY LEN(s2.Suburb) DESC) s1

Image 3 Image 4 Image 5 Image 6 Image 7 Image 8


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand

Comments and Discussions

QuestionMt Eden versus Mount Eden Pin
Member 1049261128-Dec-13 20:03
memberMember 1049261128-Dec-13 20:03 
Questionanother way... Pin
obiewoncanoli18-Jul-11 16:28
professionalobiewoncanoli18-Jul-11 16:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 23 Mar 2010

Tagged as


5 bookmarked