November 24, 2009 at 9:19 am
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:
November 24, 2009 at 9:48 am
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)
November 24, 2009 at 9:56 am
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:
November 24, 2009 at 12:22 pm
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:
November 24, 2009 at 1:33 pm
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) ).
November 24, 2009 at 2:05 pm
I get all zeroes.
CHARINDEX(CHAR(9), ItemNo, 0)
Btw.. do I change the 9?
--
:hehe:
November 24, 2009 at 2:09 pm
yes, change 9 to 10 and change 9 to 13. do you get zeroes for both?
November 24, 2009 at 2:12 pm
yep getting zeroes for 9, 10 and 13
--
:hehe:
November 24, 2009 at 2:45 pm
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. 🙂
December 1, 2009 at 8:17 am
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