Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

SQL: Show the area when the address field included

Dear,

I had the following information in the customer database:

CustomerID  Customer_Sex  Customer_Address
-------------------------------------------------------
1                 M                    1 Java Road, USA
2                 F                     Flat 1210, 5 Rich Road, Canada
3                 F                     10 Life Road, USA

I would like to show the "USA" or "Canada" when the address is included, such as:

CustomerID  Customer_Sex  Address
-------------------------------------------------------
1                 M                    USA
2                 F                     Canada
3                 F                     USA

My customer database just have "USA" and "Canada" customer. What is the SQL can do that?

Please help!
Thank You!
0
charles_cp
Asked:
charles_cp
  • 5
  • 4
  • 3
  • +2
1 Solution
 
rafranciscoCommented:
Try this:

SELECT CustomerID, Customer_Sex, RIGHT(Customer_Address, CHARINDEX(' ', REVERSE(Customer_Address)) - 1) AS Address
FROM YourTable
0
 
HilaireCommented:
Please try

select a.CustomerID,  a.Customer_Sex,  b.Address
from <YourTable> a
inner join (select 'Canada' as Address, '%Canada%' as srch union all select 'USA', '%USA%') on a.Customer_Address like b.srch
0
 
pdrgCommented:
fyi - rafrancisco's answer will scale accross multiple countries more easily than Hilaire's
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
HilaireCommented:
@prdg
>>rafrancisco's answer will scale accross multiple countries more easily than Hilaire's<<
...but will fail if there happens to be a trailing space
or if the country name is not in last position
or if there happens to be no space at all (in this case it will raise an error)

the example above is just propose an alternative solution that might be more robust

as for the scalability,
i assume there's a country table somewhere in the DB ?
if so, a very scalable solution is

select a.CustomerID,  a.Customer_Sex,  b.CountryName
from <YourTable> a
inner join tblCountry b on a.Customer_Address like '%' + b.CountryName + '%'

your feedback is much appreciated.  But please let the asker decide which solution fits his requirement best

Regards
Hilaire
0
 
pdrgCommented:
There's a strong chance the questioner was asking for help precisely because they needed guidance, and I was just trying to help out.

btw, if someone has an address of #3, Kusak drive, somewhereville, Canada, that would match %canada% AND %USA%, so it's a solution, just not failsafe.

0
 
HilaireCommented:
Please accept my sincere apologies.
I'm a little upset today.

>>
btw, if someone has an address of #3, Kusak drive, somewhereville, Canada, that would match %canada% AND %USA%, so it's a solution, just not failsafe.
<<

it's a constructive note ... Thanks for pointing out
0
 
pdrgCommented:
:¬)

Still friends, then :¬)
0
 
Scott PletcherSenior DBACommented:
I suggest you create a trigger that sets a bit flag (or tinyint if you want to allow for other countries) *once* when data is inserted/updated.  After that, *all* other code should go by the flag.  If you attempt to do this in queries, you will end up doing it in multiple places and changing the logic will be a nightmare.

Second choice would be to add a computed column to the table:

ALTER TABLE customer
ADD Country AS
--hope 'Canada' is not misspelled! (you could add common misspellings if desired, of course)
CASE WHEN RIGHT(customer_address, 6) = 'Canada' THEN 'Canada' ELSE 'USA' END


And from then on simply:

SELECT CustomerID,  Customer_Sex, Country
FROM customer

SQL Server will dynamically populate the Country only when needed and will never use disk space by storing it in the table.
0
 
pdrgCommented:
fine by me :)
0
 
HilaireCommented:
sounds like a good idea ...
BTW congratulations ScottPletcher for your volunteering as a Page Editor

charles_cp sorry for this question hijack, I guess these comments will end up begin removed anyway.

Do you any feedback/questions on the comments above.
How many different countries should the code handle ?
Is country always present in the customer_address ? always at the end ?
0
 
charles_cpAuthor Commented:
Dear Hilaire ,

I had try the following code on SQL Query Analyzer. It shows "Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'on'. "

select a.CustomerID,  a.Customer_Sex,  b.Address
from <YourTable> a
inner join (select 'Canada' as Address, '%Canada%' as srch union all select 'USA', '%USA%') on a.Customer_Address like b.srch
0
 
charles_cpAuthor Commented:
Dear All,

Thank You for your help!
I had finish the job by the following SQL:

SELECT
  CustomerID, Customer_Sex
  Case When Customer_Address like '%USA%' Then 'USA'
  else  'Canada'
  End
FROM Customer

That is like rafrancisco's solution. If I give the point for rafrancisco is OK or not?

Charles
0
 
rafranciscoCommented:
I think your solution is more like Hilaire's solution.
0
 
charles_cpAuthor Commented:
BTW, Thank you very much!
0
 
pdrgCommented:
Actually, your selected solution is most like Hilaire's solution - but beware of the gotcha I mentioned above - #3, Kusak Drive will also match %USA%, so I suggest you go with the inverse - go with (assuming *only USA and Canada addresses, and by default they will be USA unless stated otherwise)

SELECT
  CustomerID, Customer_Sex
  Case When Customer_Address like '%Canada%' Then 'Canada'
  else  'USA'
  End
FROM Customer

This is based on the idea that 'Canada' is less likely to appear as a part of a longer string, in the way 'USA' appears in 'Kusak'.  It's not a perfect solution though - there may still be occasions where Canada appears in the address as well as USA (eg '#3 Canada Gardens, somewhere, USA', which will, in this case, match 'Canada').  Also, any addresses NOT in Canada or USA will appear to be in USA (Case 'else')

You can see validating user input is messy - can you capture the address country separately when you gather the data in the first place?  It'll save you the mis-matches which you *will* get otherwise.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now