Using MAX and LTRIM, RTRIM together

  • Hello,

    I'm trying to trim the trailing spaces in my column data using below t-sql.

    MAX(LTRIM(RTRIM(column_Name)))

    Addt'l info, I use the max keyword to successfully group my data. Could the max be effecting the trimming functionality?

    Problem is even after using the above t-sql, the column data's trailing spaces do not get trimmed. Screenshot attached. Please look at the fourth column from the right.

    Thanks,

    S

    --
    :hehe:

  • Slick84 (11/24/2009)


    Hello,

    I'm trying to trim the trailing spaces in my column data using below t-sql.

    MAX(LTRIM(RTRIM(column_Name)))

    Addt'l info, I use the max keyword to successfully group my data. Could the max be effecting the trimming functionality?

    Problem is even after using the above t-sql, the column data's trailing spaces do not get trimmed. Screenshot attached. Please look at the fourth column from the right.

    Thanks,

    S

    MAX doesn't affect the trimming functionality. It's your column value; it has a bracket (']') that's causing the issue. RTRIM will removes spaces after ']' not before that.

    Try this:

    MAX(RTRIM(LTRIM(REPLACE(column_name,']','')))+']')

    HTH,

    Supriya

    Edit: You should have said so in the first place. :pinch:. Just looking at the attached screenshot it seemed like the brackets are part of the column values. Can you maybe share your select query? (without adding the brackets, of course)

  • the data doesnt have those brackets, I put them in to see if the spaces were still there or not.. as you can it tells me that there are spaced in there. im not using brackets when i trim.

    --
    :hehe:

  • Supriya.S (11/24/2009)


    Slick84 (11/24/2009)


    Hello,

    I'm trying to trim the trailing spaces in my column data using below t-sql.

    MAX(LTRIM(RTRIM(column_Name)))

    Addt'l info, I use the max keyword to successfully group my data. Could the max be effecting the trimming functionality?

    Problem is even after using the above t-sql, the column data's trailing spaces do not get trimmed. Screenshot attached. Please look at the fourth column from the right.

    Thanks,

    S

    MAX doesn't affect the trimming functionality. It's your column value; it has a bracket (']') that's causing the issue. RTRIM will removes spaces after ']' not before that.

    Try this:

    MAX(RTRIM(LTRIM(REPLACE(column_name,']','')))+']')

    HTH,

    Supriya

    Edit: You should have said so in the first place. :pinch:. Just looking at the attached screenshot it seemed like the brackets are part of the column values. Can you maybe share your select query? (without adding the brackets, of course)

    Hello Supriya,

    Please see the code below.

    SELECTMAX(LTRIM(RTRIM(Column1))),

    MAX(LTRIM(RTRIM(Column2))),

    MAX(LTRIM(RTRIM(Column3))),

    MAX(LTRIM(RTRIM(Column4))),

    LTRIM(RTRIM(Column5)),

    MAX(LTRIM(RTRIM(Column6))),

    MAX(LTRIM(RTRIM(Column7))),

    MAX(LTRIM(RTRIM(Column8))),

    MAX(LTRIM(RTRIM(Column9))),

    MAX(LTRIM(RTRIM(Column10))),

    MAX(LTRIM(RTRIM(Column11))),

    CASE MAX((LTRIM(RTRIM(Column12))))

    WHEN '' THEN 'None'

    WHEN '-' THEN 'None'

    ELSE MAX(LTRIM(RTRIM(Column12)))

    END,

    CASE MAX(LTRIM(RTRIM(REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    Column12,

    ' 7/8','.875'),

    ' 3/8','.375'),

    ' 3/4','.75'),

    ' 1/8','.125'),

    ' 1/4','.25'),

    ' 1/2','.5'),

    '-7/8','.875'),

    '-3/8','.375'),

    '-3/4','.75'),

    '-1/8','.125'),

    '-1/4','.25'),

    '-1/2','.5'))))

    WHEN '' THEN 'None'

    WHEN '-' THEN 'None'

    ELSE MAX(LTRIM(RTRIM(REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    Column12,

    ' 7/8','.875'),

    ' 3/8','.375'),

    ' 3/4','.75'),

    ' 1/8','.125'),

    ' 1/4','.25'),

    ' 1/2','.5'),

    '-7/8','.875'),

    '-3/8','.375'),

    '-3/4','.75'),

    '-1/8','.125'),

    '-1/4','.25'),

    '-1/2','.5'))))

    END,

    MAX(LTRIM(RTRIM(Column13))),

    CASE MAX((LTRIM(RTRIM(Column13))))

    WHEN '' THEN 'None'

    WHEN '-' THEN 'None'

    ELSE MAX(LTRIM(RTRIM(Column13)))

    END,

    MAX(LTRIM (RTRIM(Column13))),

    CASE MAX((LTRIM(RTRIM(Column13))))

    WHEN '' THEN 'None'

    WHEN '-' THEN 'None'

    ELSE MAX(LTRIM(RTRIM(Column13)))

    END,

    MAX(LTRIM(RTRIM(Column14))),

    MAX(LTRIM(RTRIM(Column15))),

    '0',

    NULL,

    getDate()

    FROM[server].[db].[dbo].[view] A

    GROUP BY Column5

    Column4 is the troublemaker! Trailing spaces won't get trimmed. The source is CHAR(20) from where I do the select. This select statement is part of an INSERT statement and the destination I insert into is a VARCHAR(20). I had it as CHAR but changed it to VARCHAR thinking it would fix the problem, but as you can see even after inserting into the VARCHAR data type its still got the trailing spaces.

    Confused.

    Thanks for your help bud,

    S

    --
    :hehe:

  • Can you pls. run the below query?

    SELECT CHARINDEX(CHAR(9),Column4, 0) FROM [server].[db].[dbo].[view]

    Do you get values other than 0 in the result set? If yes than you don't have trailing spaces but tabs in Column4 values. RTRIM and LTRIM do not trim tabs. You may want to replace tabs with empty strings. That's the only thing I can think of.

    -Supriya

    EDIT: Also look for Line feeds( char(10) ) and Carriage returns ( char(13) ).

  • I get all zeroes.

    CHARINDEX(CHAR(9), ItemNo, 0)

    Btw.. do I change the 9?

    --
    :hehe:

  • yes, change 9 to 10 and change 9 to 13. do you get zeroes for both?

  • yep getting zeroes for 9, 10 and 13

    --
    :hehe:

  • Slick84 (11/24/2009)


    I get all zeroes.

    CHARINDEX(CHAR(9), ItemNo, 0)

    Btw.. do I change the 9?

    Is "ItemNo" the right column name? Just wanted to make sure. 🙂

  • Yes it is. Sorry, was out for the holidays....

    --
    :hehe:

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply