SQL Server Remove all trailing commas

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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Kyaw WannaCommented:
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
 
ZberteocCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ZberteocCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@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
 
Scott PletcherSenior DBACommented:
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
 
ZberteocCommented:
@Jim,

Can you do something with those file so that we get to travel for free? :o)))
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ZberteocCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
This works for me
Sorry for the late get back
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.