0

I am working on SQL. I have a situation here where I need to check whether the length of a particular column (WarningNote) is zero or not. Based on it I have to give values to the output column. The problem that i face is, for a particulare id, the warningnote columns is displaying a blank field. I did a LTRIM and RTRIM on the that value and took the length of the column expecting it give 0 as the answer. But the answer displayed is 2. My doubt is when I do LTRIM and RTRIM on white space, and take the length of the field, then it should be zero right. Can anyone help me on this?

Geethu
  • 315
  • 1
  • 6
  • 11
  • Check this question, and try running the query in the answer: http://stackoverflow.com/questions/1337196/sql-query-for-a-carriage-return-in-a-string-and-ultimately-removing-carriage-ret – AHiggins Jul 03 '14 at 16:03
  • 1. Show how you're using `LTRIM` and `RTRIM`, 2. the string could contain non-printable characters that aren't "blanks", – D Stanley Jul 03 '14 at 16:03
  • Can you show the code you used when you did the TRIMs and got the length of 2? – Tab Alleman Jul 03 '14 at 16:04
  • (SELECT LTRIM(RTRIM(REPLACE(REPLACE(Warning_Note,' ',''),'',''))) from (SELECT CASE WHEN (RTRIM(LTRIM(Exp_Note)))!= '' THEN Warning_Note + '-' +CHAR(13)+CHAR(10) + Exp_Note +CHAR(10)+CHAR(13) +CHAR(10)+CHAR(13) ELSE Warning_Note +CHAR(10)+CHAR(13) +CHAR(10)+CHAR(13) END FROM #TEMP1 p3 WHERE p3.SongCode=p1.SongCode GROUP BY Warning_Note, Exp_Note,Severity, SequenceNumber ORDER BY Severity, SequenceNumber FOR XML PATH (''))Y(Warning_Note)) AS WarningNote – Geethu Jul 03 '14 at 16:10
  • Its a part of a subquery in a procedure – Geethu Jul 03 '14 at 16:11
  • What RDBMS are you using? – Mureinik Jul 03 '14 at 16:34
  • The code you posted doesn't appear to have a call to `LENGTH()` in it. Are you calling `LENGTH()` on the *original column* or on the resulting *value*? – Dan Getz Jul 03 '14 at 17:47

0 Answers0