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: