Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Remove all trailing commas

Posted on 2016-08-25
10
Medium Priority
?
471 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 66

Accepted Solution

by:
Jim Horn earned 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 66

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 70

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 66

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

580 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