We help IT Professionals succeed at work.

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?
Comment
Watch Question

Could you show some samples of CustomerName (remove any sensitive data)?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
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

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
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
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

Karen SchaeferBI ANALYST

Author

Commented:
Thanks for  your inputs.