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!
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!
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
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
>>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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:¬)
Still friends, then :¬)
Still friends, then :¬)
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.
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 ?
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 ?
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
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
ASKER
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
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.
ASKER
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.
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.
SELECT CustomerID, Customer_Sex, RIGHT(Customer_Address, CHARINDEX(' ', REVERSE(Customer_Address))
FROM YourTable