asked on Remove State from customer Name
Need help with stripping out the city and State name from the Customer Name field (TKSuppliers (ABCity, TX)
what am I missing?
SELECT CustomerName
,LEFT(CustomerName, CHARINDEX(',', CustomerName) - 1) AS [Surname]
,REPLACE(SUBSTRING(CustomerName, CHARINDEX(',', CustomerName), LEN(CustomerName)), '(', '') AS CitySt
FROM Sales.Customers CUST
what am I missing?
DatabasesMicrosoft SQL ServerSQL
Last Comment
Karen Schaefer
Could you show some samples of CustomerName (remove any sensitive data)?
This would basically do..
And incorporated in your code like this..
declare @a varchar(100) = 'TKSuppliers (ABCity, TX)'
SELECT @a, charindex(' (', @a), charindex(', ',@a)
; with cte as
(select @a a, len(@a) len, charindex(' (', @a) first , charindex(', ',@a) second
)
select a, substring(a, 1, first) Customer, substring(a, first+2, second-first-2) City, ltrim(replace(right(a, len-second), ')', '')) state
from cte
And incorporated in your code like this..
; with cte as (
SELECT CustomerName
, len(CustomerName) len, charindex(' (', CustomerName) first , charindex(', ',CustomerName) second
FROM Sales.Customers
)
SELECT CustomerName
, substring(CustomerName, 1, first-1) Customer, substring(CustomerName, first+2, second-first-2) City, ltrim(replace(right(CustomerName, len-second), ')', '')) state
FROM cte
I assume that "(TKSuppliers (ABCity, TX)" is an example of your names, but that the "(" at the beginning isn't actually included in the name (so the actual string is "TKSuppliers (ABCity, TX)" ).
The first problem is that you're looking for a comma ( CHARINDEX(',', CustomerName) ), but, in your example, the comma is used to separate the city and state. The way that I read it, you're extracting everything before the comma for Surname, and everything starting with the comma for CitySt. For your example, that would give you "TKSuppliers (ABCity" and ", TX)".
If the names will always have that form, you could try changing both CHARINDEX functions to look for "(", instead of ",". I think that that would give you "TKSuppliers " and "ABCity, TX)". If that works, you might want to add another Replace to remove the trailing ")" on CitySt.
But all of that only works if the names always have that form. If the customer name could contain a "(", or if the city and state could be separated from the name by something other than "(", then simply looking for the first "(" won't work for those names. And if the city and state could contain a ")", then adding a Replace to remove ")" could also be a problem.
James
The first problem is that you're looking for a comma ( CHARINDEX(',', CustomerName) ), but, in your example, the comma is used to separate the city and state. The way that I read it, you're extracting everything before the comma for Surname, and everything starting with the comma for CitySt. For your example, that would give you "TKSuppliers (ABCity" and ", TX)".
If the names will always have that form, you could try changing both CHARINDEX functions to look for "(", instead of ",". I think that that would give you "TKSuppliers " and "ABCity, TX)". If that works, you might want to add another Replace to remove the trailing ")" on CitySt.
But all of that only works if the names always have that form. If the customer name could contain a "(", or if the city and state could be separated from the name by something other than "(", then simply looking for the first "(" won't work for those names. And if the city and state could contain a ")", then adding a Replace to remove ")" could also be a problem.
James
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your inputs.