Remove Multiple Trailing Characters in SQL Server - Oracle equivalent to Trim (Trailing)

Question

I have a table with a field that acts like an array for some front-end processing and that is the result of concatenating 9 columns from a different flat table. The field was populated as such

Field1 + ',' + Field2 + ',' + Field3 + ',' + Field4 + ',' + Field5 + ',' + Field6 + ',' + Field7 + ',' + Field8 + ',' + Field9

If Field4 from the flat table has a value, then Fields 1, 2 and 3 also have a value. But, most of the time Fields 5 – 9 do not have values. The result is that most values in the array field (after the concatenation) look like this:

Bob,Joe,Mary,Sam,,,,,

I am looking for a simple way to update this concatenated field to instead look like this:

Bob,Joe,Mary,Sam

This is the Oracle equivalent to Trim(Trailing',' From FieldValue)

dfundako · Answer 1 · 2016-05-23T19:55:53.993

3

I would go with a reverse, find the first non-comma char, then use that in conjunction with a LEN() and LEFT()

DECLARE @string VARCHAR(200) = 'Bob,Joe,Mary,Sam,,,,,'

SELECT LEFT(@string, LEN(@string) - PATINDEX('%[^,]%', REVERSE(@string))+1)

edited May 23 '16 at 19:55

answered May 23 '16 at 19:48

dfundako

8,022
3
18
34

--This is cool, but failes for the following: DECLARE @string VARCHAR(200) = 'Bob1,Joe2,Mary3,Sam4,,,,,' SELECT LEFT(@string, LEN(@string) - PATINDEX('%[A-Z]%', REVERSE(@string))+1) – Gregory Hart May 23 '16 at 19:56
The code has been updated to find the first non-comma character thanks to Martin's suggestion. – dfundako May 23 '16 at 19:57

score 1 · Answer 2 · answered May 23 '16 at 19:43

Create the following UDF:

CREATE FUNCTION Trailing(@String VarChar(Max), @Char VarChar(5))
RETURNS VarChar(Max)
BEGIN
    RETURN
        Reverse(
        SubString(
        Reverse(@String)
        ,PatIndex('%[^' + @Char + ' ]%',Reverse(@String))
        ,DataLength(@String)
        )
        )
END
GO

Give it a test drive:

SELECT
    'Array_Value_1,Array_Value_2,Array_Value_3,,,,,' AS Original_String,
    dbo.Trailing('Array_Value_1,Array_Value_2,Array_Value_3,,,,,', ',') AS Clean_String
GO

Personally I wouldn't reverse the string three times. You need to do it once but then you can calculate the bit of the string you need by subtracting the patindex from the length. — Martin Smith, May 23 '16 at 19:44

score 1 · Answer 3 · answered May 23 '16 at 19:48

If you simply do this:

SELECT REPLACE(@str, ',,', '');

You will be left with a string with 1 or 0 commas at the end, depending on whether there were an even or odd number of trailing commas.

You can wrap this in a SUBSTRING or LEFT construct that removes the last character if it's a comma.

Remove Multiple Trailing Characters in SQL Server - Oracle equivalent to Trim (Trailing)

3 Answers3