Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using MAX and LTRIM, RTRIM together Expand / Collapse
Author
Message
Posted Tuesday, November 24, 2009 9:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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


--


  Post Attachments 
1.jpg (36 views, 46.81 KB)
Post #823973
Posted Tuesday, November 24, 2009 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 3:59 PM
Points: 315, Visits: 678
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. . 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)
Post #823989
Posted Tuesday, November 24, 2009 9:56 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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.

--
Post #823997
Posted Tuesday, November 24, 2009 12:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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. . 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.

SELECT		MAX(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


--
Post #824076
Posted Tuesday, November 24, 2009 1:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 3:59 PM
Points: 315, Visits: 678
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) ).

Post #824122
Posted Tuesday, November 24, 2009 2:05 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
I get all zeroes.

CHARINDEX(CHAR(9), ItemNo, 0)


Btw.. do I change the 9?


--
Post #824140
Posted Tuesday, November 24, 2009 2:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 3:59 PM
Points: 315, Visits: 678

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

Post #824144
Posted Tuesday, November 24, 2009 2:12 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
yep getting zeroes for 9, 10 and 13

--
Post #824145
Posted Tuesday, November 24, 2009 2:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 3:59 PM
Points: 315, Visits: 678
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.
Post #824162
Posted Tuesday, December 1, 2009 8:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
Yes it is. Sorry, was out for the holidays....

--
Post #826721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse