Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

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)

SELECT   CustomerName
        ,LEFT(CustomerName, CHARINDEX(',', CustomerName) - 1) AS [Surname]
        ,REPLACE(SUBSTRING(CustomerName, CHARINDEX(',', CustomerName), LEN(CustomerName)), '(', '') AS CitySt
FROM    Sales.Customers CUST 

Open in new window


what am I missing?
Avatar of als315
als315
Flag of Russian Federation image

Could you show some samples of CustomerName (remove any sensitive data)?
This would basically do..
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

Open in new window


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 

Open in new window

Avatar of James0628
James0628

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
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia 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
Avatar of Karen Schaefer

ASKER

Thanks for  your inputs.