Solved

SQL Server Remove all trailing commas

Posted on 2016-08-25
10
97 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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