Solved

SQL Server Remove all trailing commas

Posted on 2016-08-25
10
60 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
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 26

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
 
LVL 26

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Expert Comment

by:ScottPletcher
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 26

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 26

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now