CODEWITHSUNDEEP
sql-servervb.net
kodym
kodym

Reputation: 47

Removing leading and trailing commas

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

Sample

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.

Upvotes: 0

Views: 11963

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81990

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

Upvotes: 3

Jigar Joshi
Jigar Joshi

Reputation: 56

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

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112537

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

Upvotes: 3

S3S
S3S

Reputation: 25132

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

Upvotes: 1

Related Questions