SQL Server Performance Forum – Threads Archive
To find trailing space
I want to see trailing space for some records in the table. Table name is fund_status. Q1)select ‘fund_status_cd’=’>’+fund_status_cd+'<‘,’descr’+'<‘+descr+'<‘ from fund_status
–Result shows trailing spaces
>C/D <
>C/W < Q2)
SELECT REPLACE(fund_status_cd,#%92 ‘,#%92x#%92)
From fund_status
–Result shows no trailing spaces
C/D
C/W Q3)
select len(fund_status_cd),len(rtrim(fund_status_cd))
from fund_status
–result shows no trailing spaces
3
3 Why I am getting different results? Regards, Ashish
Ashish Johri
try
select len(fund_status_cd), datalength(fund_status_cd)
from fund_status —
kb
http://kbupdate.info/ |http://suppline.com/
For a CHAR column, DATALENGTH() returns the declared length of the column, regardless of the number of characters. For a VARCHAR column, DATALENGTH() returns the length of the string of characters, including any trailing spaces. For both, LEN() returns the number of characters excluding any trailing spaces.
Thanks tz71 and Adriaan. Adriaan, Your responses are always 100% accurate. Ashish Johri
But Adriaan when we run this query as below: select len(fund_status_cd),len(rtrim(fund_status_cd))
from fund_status Then because there are some trailing spaces this should give different values of len(fund_status_cd) and
len(rtrim(fund_status_cd). Am I correct? Ashish Johri
Select len(‘test ‘), len(rtrim(‘test ‘)) Madhivanan Failing to plan is Planning to fail
Ashish, what is the data type of your fund_status_cd column?
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Then because there are some trailing spaces this should give different values of len(fund_status_cd) and <br />len(rtrim(fund_status_cd).<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">LEN(<varchar_column><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> and LEN(<char_column><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> both return the length without trailing spaces, so RTRIM() is irrelevant!<br /><br />If you have a column of the CHAR data type, using DATALENGTH is pointless since you always get the full column size (except when the column is null).<br /><br />Comparing DATALENGTH(<varchar><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> and LEN(<varchar><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> is one way to check for trailing spaces:<br /><br />SELECT fund_status_cd<br />FROM fund_status<br />WHERE DATALENGTH(fund_status_cd) > LEN(fund_status_cd)<br /><br />You can also use a CASE expression with a LIKE operator:<br /><br />SELECT fund_status_cd, CASE WHEN fund_status_cd LIKE ‘% ‘ THEN 1 ELSE 0 END<br />FROM fund_status<br /><br />… or with the two length functions:<br /><br />SELECT fund_status_cd,<br />CAST(DATALENGTH(ISNULL(fund_status_cd, ”)) – LEN(ISNULL(fund_status_cd,”)) AS VARCHAR(10)) + ‘ trailing space(s).'<br />FROM fund_status
Sorry Adriaan for sending a late reply. Thanks Adriaan for sending many options to solve the problem. This means my query to find trailing spaces also holds good. select ‘fund_status_cd’=’>’+fund_status_cd+'<‘,’descr’+'<‘+descr+'<‘ from fund_status
Regards,
Ashish Ashish Johri
]]>