Link to home
Start Free TrialLog in
Avatar of charles_cp
charles_cp

asked on

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!
Avatar of rafrancisco
rafrancisco

Try this:

SELECT CustomerID, Customer_Sex, RIGHT(Customer_Address, CHARINDEX(' ', REVERSE(Customer_Address)) - 1) AS Address
FROM YourTable
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
fyi - rafrancisco's answer will scale accross multiple countries more easily than Hilaire's
@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
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.

ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:¬)

Still friends, then :¬)
Avatar of Scott Pletcher
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.
fine by me :)
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 ?
Avatar of charles_cp

ASKER

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
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
I think your solution is more like Hilaire's solution.
BTW, Thank you very much!
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.