SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using MAX and LTRIM, RTRIM together


Using MAX and LTRIM, RTRIM together

Author
Message
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
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
Attachments
1.jpg (48 views, 46.00 KB)
Supriya.S
Supriya.S
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 679
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)
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
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
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
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.


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

--
Hehe
Supriya.S
Supriya.S
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 679
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) ).
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
I get all zeroes.

CHARINDEX(CHAR(9), ItemNo, 0)


Btw.. do I change the 9?

--
Hehe
Supriya.S
Supriya.S
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 679
yes, change 9 to 10 and change 9 to 13. do you get zeroes for both?
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
yep getting zeroes for 9, 10 and 13

--
Hehe
Supriya.S
Supriya.S
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 679
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. :-)
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 1163
Yes it is. Sorry, was out for the holidays....

--
Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search