Avatar of Karen Schaefer
Karen Schaefer
Flag 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?
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Karen Schaefer

8/22/2022 - Mon

Could you show some samples of CustomerName (remove any sensitive data)?
Raja Jegan R

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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Wills

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Karen Schaefer

Thanks for  your inputs.