Removing leading and trailing commas

Question

I am trying to find a way to remove trailing and leading commas in the SELECT statement. Here is some sample data:

SELECT
    GRAIN, MATERIAL, BACKING, GRITS,
    REPLACE(LTRIM(RTRIM(REPLACE(PROPERTIES, ',', ' '))), ' ', ',') PROPERTIES,
    SPECIAL, APPLICATION, PRODUCTTYPE
FROM PRODUCTFINDER

I tried using trim, rtrim, and ltrim but none of them changed the strings.. Idk if I was using the wrong syntax or what, but could someone help me please?

I am using SQL Server 2008.

Perhaps you could share your select statement so we aren't guessing what you are doing? — Sean Lange, Jul 26 '18 at 14:57
And if possible, it would be better to prevent these junk characters getting stored in the first place — A Friend, Jul 26 '18 at 14:57
ltrim and rtrim remove leading and trailing spaces. Those functions don't remove commas. Could you share some more details? It would be really helpful to see your table and data. Here is a great example of what to post. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ But it looks like the real problem is the data. — Sean Lange, Jul 26 '18 at 15:02
@SeanLange i have a picture with the table and data as a link in the question but i can't embed it because i'm not high enough rep — kodym, Jul 26 '18 at 15:03
Pictures are not good. Why? https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — Sean Lange, Jul 26 '18 at 15:03
I would probably put this data into multiple tables (4 exactly) a header and 3 detail tables. For your issue... I would use a string splitter and get rid of the nulls. This would remove the interior nulls though. — KeithL, Jul 26 '18 at 15:33
See my answer here: https://stackoverflow.com/questions/7838676/sql-server-trim-character/14647204#14647204 — Teejay, Jul 26 '18 at 15:40

John Cappelletti · Accepted Answer · 2018-07-26T16:00:31.907

3

Just another option.

This is a non-destructive approach that will eliminate any number of repeating commas and forces a final cleanup via the double pipes

For the expansion,reduction, and elimination I picked two obscure characters †‡

Example

Declare @S varchar(max) =',,,,Some,,,,,Content,,,'

Select
    replace(
        replace(
            replace(
                replace(
                    replace('||,' + @S + ',||', ',', '†‡'), 
                    '‡†', ''
                ),
                '†‡', ','
            ),
            '||,', ''
        ),
        ',||', ''
    )

Returns

Some,Content

EDIT - Removed the LTRIM()/RTRIM()

edited Jul 26 '18 at 16:00

answered Jul 26 '18 at 15:43

John Cappelletti

79,615
7
44
66

@kodym Happy to help. – John Cappelletti Jul 26 '18 at 15:50
1

neat approach too, though confusing at first +1 – S3S Jul 26 '18 at 15:58
@scsimon Full Disclosure: Gordon demonstrated this technique several months ago use <> , I just use more obsure characters to avoid conficts with HTML – John Cappelletti Jul 26 '18 at 16:01

Olivier Jacot-Descombes · Answer 2 · 2018-07-26T15:47:54.857

Try this:

SELECT
    GRAIN, MATERIAL, BACKING, GRITS,
    TRIM(',' FROM PRODUCTFINDER.PROPERTIES) AS PROPERTIES,
    TRIM(',' FROM PRODUCTFINDER.SPECIAL) AS SPECIAL,
    TRIM(',' FROM PRODUCTFINDER.APPLICATION) AS APPLICATION,
    TRIM(',' FROM PRODUCTFINDER.PRODUCTTYPE) AS PRODUCTTYPE
FROM PRODUCTFINDER

I am not sure which columns you want to trim.

This variant of TRIM (Transact-SQL) is available since SQL-Server 2017.

If you have an earlier version of SQL-Server, do this in the Font-End (VB). This also gives you the possibility to replace multiple commas by single ones in the middle of the text.

Dim s = ",,,Abc,,,Def,Xyz,,,"
Console.WriteLine(Regex.Replace(s, ",{2,}", ",").Trim(","c))

Prints

Abc,Def,Xyz

Regex.Replace(s, ",{2,}", ",") uses the a regular expression ,{2,} to find 2 or more occurrences of commas and replaces them by one single comma. .Trim(","c) removes leading and trailing commas.

For Regex you need a

Imports System.Text.RegularExpressions

Another variant uses string split with the RemoveEmptyEntries option and then joins the parts again to form the result.

Dim s = ",,,Abc,,,Def,Xyz,,,"
Dim parts As String() = s.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries)
Console.WriteLine(String.Join(",", parts))

This breaks the select statement, i have SQL SERVER 2008 – kodym Jul 26 '18 at 15:04 — kodym, Jul 26 '18 at 15:04

S3S · Answer 3 · 2018-07-26T15:32:57.090

Here's one method using PATINDEX with LEFT and RIGHT.

declare @var varchar(64)= ',,,,,,,,asdf,dsf,sdfsd,asdf,,,,,,,,'

select 
    left(right(@var,len(@var) - patindex('%[^,]%',@var) + 1)
        ,len(right(@var,len(@var) - patindex('%[^,]%',@var) + 1)) - patindex('%[^,]%',reverse(right(@var,len(@var) - patindex('%[^,]%',@var) + 1))) + 1)

Just change @var to your column name.

This code strips the leading commas by searching for the first value that isn't a comma, via patindex('%[^,]%',@var) and takes everything to the RIGHT of this character. Then, we do the same thing using LEFT to remove the trailing commas.

select 
   Special =  left(right(Special,len(Special) - patindex('%[^,]%',Special) + 1),len(right(Special,len(Special) - patindex('%[^,]%',Special) + 1)) - patindex('%[^,]%',reverse(right(Special,len(Special) - patindex('%[^,]%',Special) + 1))) + 1)
   ,[Application] = left(right([Application],len([Application]) - patindex('%[^,]%',[Application]) + 1),len(right([Application],len([Application]) - patindex('%[^,]%',[Application]) + 1)) - patindex('%[^,]%',reverse(right([Application],len([Application]) - patindex('%[^,]%',[Application]) + 1))) + 1)
   ,[ProductType] = left(right([ProductType],len([ProductType]) - patindex('%[^,]%',[ProductType]) + 1),len(right([ProductType],len([ProductType]) - patindex('%[^,]%',[ProductType]) + 1)) - patindex('%[^,]%',reverse(right([ProductType],len([ProductType]) - patindex('%[^,]%',[ProductType]) + 1))) + 1)
FROM PRODUCTFINDER

score 0 · Answer 4 · answered Jul 26 '18 at 16:00

0

SQL Server is not ideal place for manipulating strings so trim logic should be at programming level

As far as trimming particular character is required in query, refer to below thread

Trimming any Leading or trailing characters

answered Jul 26 '18 at 16:00

Jigar Joshi

56
4

Removing leading and trailing commas

4 Answers4