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?
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Could you show some samples of CustomerName (remove any sensitive data)?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

James0628Commented:
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
Mark WillsTopic AdvisorCommented:
If wanting the names inside the brackets, comma separated to give 3 individual columns then use substring every time
SELECT   CustomerName
        ,ltrim(substring(CustomerName, CHARINDEX('(', CustomerName) + 1,CHARINDEX('(', CustomerName,2) - CHARINDEX('(', CustomerName) - 1)) AS [custname]
        ,ltrim(substring(CustomerName, CHARINDEX('(', CustomerName,2) + 1,Charindex(',',customername) - CHARINDEX('(', CustomerName,2) - 1)) AS [city]
        ,ltrim(substring(CustomerName, CHARINDEX(',', CustomerName,charindex('(',customername,2))+1, charindex(')',customername) - 1 - CHARINDEX(',', CustomerName,charindex('(',customername,2)) )) AS CitySt
FROM    (values ('(TKSuppliers (ABCity, TX)'),('(abc(Def,ghi)'),('(name   (city   ,   state)')) CUST(CustomerName) 
WHERE customername like '(%(%,%)'

Open in new window

The nice feature of CHARINDEX is you can add a third element to represent the starting position...

So, the challenge above is to identify those delimited strings - being firstly "(TKSuppliers (" then secondly "(ABCity, " and thirdly  ", TX)" and you can see an overlap, the first one ends where the second starts, and the second ends where the third starts. And that forms a recognisable pattern.

So long as the pattern is '(%(%,%)' it will work by identifying that pattern (regardless of spacing).

And that is really where you need to start from. Identify the pattern. Code for the pattern.

So much so, is just by using the pattern, it should still work :
SELECT   CustomerName
        ,ltrim(substring(CustomerName, CHARINDEX('(', CustomerName) + 1,CHARINDEX('(', CustomerName,2) - CHARINDEX('(', CustomerName) - 1)) AS [custname]
        ,ltrim(substring(CustomerName, CHARINDEX('(', CustomerName,2) + 1,Charindex(',',customername) - CHARINDEX('(', CustomerName,2) - 1)) AS [city]
        ,ltrim(substring(CustomerName, CHARINDEX(',', CustomerName,charindex('(',customername,2))+1, charindex(')',customername) - 1 - CHARINDEX(',', CustomerName,charindex('(',customername,2)) )) AS CitySt
FROM    (values ('(TKSuppliers (ABCity, TX)'),('(abc(Def,ghi)'),('(name   (city   ,   state)'),('(%(%,%)')) CUST(CustomerName) 
WHERE customername like '(%(%,%)'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for  your inputs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.