Solved

SQL Server Remove all trailing commas

Posted on 2016-08-25
10
156 Views
Last Modified: 2016-08-30
I have a customer that sends data to a table.
I have no control over that process and how they buld their data
But it constantly comes in with 1 - 20 trailing commas
I want to trim all leading and trailing commas in SQL Server as it comes in

So... this...
 ,,lbrister, ee-change, dog, cat, horse,,,,

Becomes this
lbrister, ee-change, dog, cat, horse
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41770502
Nice timing.  I'm wrestling with this myself.

Here's what I have so far, which uses PATINDEX('[^,%] to find the first non-comma in a string, then uses REVERSE to flip the string around and do it again for trailing commas

Declare @str varchar(100) = ',,lbrister, ee-change, dog, cat, horse,,,,'

-- Remove leading commas
SELECT @str = RIGHT(@str, LEN(@str) - PATINDEX('%[^,]%', @str) + 1)
SELECT @str

-- Remove trailing commas
SELECT @str = REVERSE(@str) 
SELECT @str = RIGHT(@str, LEN(@str) - PATINDEX('%[^,]%', @str) + 1)
SELECT @str = REVERSE(@str) 

SELECT @str

Open in new window

0
 
LVL 2

Expert Comment

by:Kyaw Wanna
ID: 41770520
Please try this :
DECLARE @str nvarchar(max)
SET @str = ',,lbrister, ee-change, dog, cat, horse,,,,'  
SET @str = REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(@str, ',', ' '))), ' ', ','),',,',',')  
PRINT @str

Open in new window

1
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41770565
DECLARE @str nvarchar(max)
SET @str = ',,lbrister, ee-change, dog, cat, horse,,,,'  
-- get rit of leading ,
while PATINDEX(',%',@str)>0
    set @str=substring(@str,2,len(@str))
-- get rit of trailing ,
while PATINDEX('%,',@str)>0
    set @str=left(@str,PATINDEX('%,',@str)-1)

Open in new window

1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Zberteoc
ID: 41770578
Be aware, though. The meaning of commas with no space between them is that there is no data in that corresponding column. If that is not the same for every line/row then you will have issues in the import process. What I mean is that you could have this:

,,,lbrister, ee-change, dog, cat, horse,,,,,
,,x,lbrister, ee-change, dog, cat, horse,,y,,,

each line has 13 fields

which will turn into
lbrister, ee-change, dog, cat, horse
x,lbrister, ee-change, dog, cat, horse,,y

first row has 5 fields while the second has 8.

How are you gonna import that?

I would say to import the empty fields as they are.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41770709
@Zberteoc - That's my world right now, as my current client is a corporate travel agency where I have to suck in a bunch of agency files from around the world that may have at one time had uniform specs, but there are now lots of differences.  

Some of these files have two columns but (16k-2) commas at the end, obviously some kind of Excel export when there were 16k column limitations.   My instructions are that these agencies can't (won't, are not able to, ..) change their files specs, and that I have to accommodate all the differences.

Yay.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41770813
Declare @str varchar(100) = ',,lbrister, ee-change, dog, cat, horse,,,,'
--Set @str = 'lbrister, ee-change, dog, cat, horse' --to test with no leading/trailing comma(s)

SELECT STUFF(LEFT(@str, LEN(@str) - PATINDEX('%[^,]%', REVERSE(@str)) + 1), 1, PATINDEX('%[^,]%', @str) - 1, '')
1
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41770834
@Jim,

Can you do something with those file so that we get to travel for free? :o)))
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41770843
Travel Agency = No non-rev (aka fly for free) privs.
Airline = Non-rev privs.

So no.

Also the 'Glory years' of non-rev travel was before 2005 before airlines got really good at online marketing to fill up planes with paying passengers.   Now it's more of a gamble.

I worked at Northwest Airlines from 1997-1999 and the privs were frEEaking awesome!   Weekend skiing in Colorado six times, day trips to DC, Chicago, weekends in Palm Springs, LA, Tampa.   Did my bachelor party in Huntington Beach, Calfornia.  Honeymoon in Paris (and London three months later) ended up getting into first class both ways.

Occasionally I would come into work in the morning and my boss would say something like 'Hey I have a hot date tonight.  Need you to fly to Winnipeg (San Diego, New York, wherever) for some meeting and be back by nine.'

Man I miss that.

The flip side though is the airlines know that whole flying for free thing has a value, so they tend to pay slightly less than market wages.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41771812
Hey Jim, that is a great story but I was actually joking with my question. :o)

 However, I had no idea that what you were telling really existed. I use flights usually in vacation to go to Europe from Canada to visit my country of origin. It would be nice to have that kind of facilities. It's quite pricey these days...
0
 

Author Closing Comment

by:lrbrister
ID: 41776907
This works for me
Sorry for the late get back
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question