Some Unexpected Result of LEN() function on REVERSED() string

I have a project that involves a number of string manipulation. The LEN() and REVERSE() functions are two of the most common functions that I use to manipulate strings for various purposes.

I have not noticed it anything wrong with it until lately. Check out the following codes:

select CharVar = ''123' - char(9) - no space', 
       CharLen = len(cast(''123'' as char(9))), 
       ReverseLen = len(reverse(cast(''123'' as char(9))))
union all
select CharVar = ''123' - char(9) - trailing 1 space', 
       CharLen = len(cast(''123' ' as char(9))), 
       ReverseLen = len(reverse(cast(''123' ' as char(9))))
union all
select CharVar = ''123' - char(9) - trailing 2 space', 
       CharLen = len(cast(''123'  ' as char(9))), 
       ReverseLen = len(reverse(cast(''123'  ' as char(9))))

As expected, the LEN() function ignored the trailing spaces. The LEN() of the returned string by the REVERSE() function, however, returned the full size of the character.

CharVar                                CharLen     ReverseLen
--------------------------------       ----------- -----------
''123'' - char(9) - no space           3           9
''123'' - char(9) - trailing 1 space   3           9
''123'' - char(9) - trailing 2 space   3           9

Now it starts to get weird...

select CharVar = ''123' - char(9) - leading 1 space', 
       CharLen = len(cast(' '123'' as char(9))), 
       ReverseLen = len(reverse(cast(' '123'' as char(9))))
union all
select CharVar = ''123' - char(9) - leading 2 space',
       CharLen = len(cast('  '123'' as char(9))), 
       ReverseLen = len(reverse(cast('  '123'' as char(9))))
union all
select CharVar = '123 - char(9) - leading 3 space', 
       CharLen = len(cast('   123' as char(9))), 
       ReverseLen = len(reverse(cast('   123' as char(9))))

Check out the results:

CharVar                            CharLen     ReverseLen
-------------------------------    ----------- -----------
'123' - char(9) - leading 1 space  4           8
'123' - char(9) - leading 2 space  5           7
'123' - char(9) - leading 3 space  6           6

The LEN() function counted the leading spaces. After the REVERSE() function, however, it seems the LEN() returns the size of the char variable less the number of trailing spaces. I was under the impression that LEN() will ignore those spaces as, after the REVERSE(), it became trailing spaces.

After further research, it looks like REVERSE() function returns the same datatype of the string being passed to it, as expected. If the character of the string passed is less than the full length of the character, reverse automatically returns a value with leading spaces.

To illustrate this, check this code:

select CharVar = 'x' + cast('123' as char(9)), 
       ReversedChar = 'x' + reverse(cast('123' as char(9)))

Here's the result:

CharVar    ReversedChar
---------- ------------
x123       x      321

The returned ReversedChar value reversed the string with the same data type. Since it's a char data type, the returned string included the expected spaces in the char variable.

I guess this is not unexpected, maybe am just not aware...

To count the number of bytes in a var/character string, use the DATALENGTH() instead.

Comment?


~~ CK

The Caveat of IsNull()

IsNullI() is a very handy function to replace the value of an expression with another, if the expression being tested is NULL. However, this function should be use with care. Remember always that the expression being checked and the replacement value should have the same data type or some unexpected (and sometimes undetectable) result may occur. As a rule, the function will return an expression with the same data type of that of the expression being checked.

Considering the following code:

declare @a as bit
select
 test_result = 
    case 
      when isnull(@a, 9) = 9 then 'Nine'
      when @a = 1 then 'One'
      when @a = 0 then 'Zero'
      else  'Unknown'
    end

The above code will never return the value 'Nine', even if at first glance, the code above should return the string 'Nine'.

What's going on? It's because the variable @a is a bit data type. The function will explicitly convert the second expression into the data type of the first expression. On the sample code, 9 will be converted into a bit expression which will return 1. So the expression isnull(@a, 9) = 9 will always be false. The entire code will not return an error and is not immediate visible.

This can also happen with strings. Consider the following code:

declare @a as varchar(5)


select isnull(@a, 'abcdefghij')

The above code will return the first 5 characters ('abcde') only. Why? Because this time, the variable @a is 5-character string. So even if it looks like the entire replacement string should have been returned by the function, again, the second expression will be converted into the first expression resulting to data truncation.

So make sure to check the data types of those two parameters.

As always, comments are welcome...


~~ CK

Replace Multiple Spaces with One

Once in a while I encounter requirements to remove multiple spaces in a string column. My most common solution is to use loop to replace multiple spaces with one. The problem starts when the value that needs cleaning is a column in huge table. There's a possibility that the operation may result in multiple table write or an RBAR (Row By Agonizing Row).

Browsing around I saw this function. The best part of this function is that it's a set-based operation which, most of the time, is faster. With permission to it's original author, I'm posting it here so I can use it next time I need it.

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN   replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
  '                                 ',' '),
  '                 ',' '),
  '         ',' '),
  '     ',' '),
  '   ',' '),
  '  ',' '),
  '  ',' ')
END

Big thanks to Michael Mierruth


 ~~ CK

Convert Column into A Delimited String, Part 1

Here's a problem that I usually encounter on the forum and at work. Sometimes I need a stored proc or function to read a table and return a single string delimited with either comma or tab or bar.
Although SQL Server 2005 included a PIVOT/UNPIVOT relational operators, I think it's still worth it to look at this solution.
Consider this table:

declare @myTable table (rownum int, rowname varchar(10))
declare @DelimitedString varchar(max)

insert @myTable values (1, 'One')
insert @myTable values (2, 'Two')
insert @myTable values (3, 'Three')
insert @myTable values (4, 'Four')
insert @myTable values (5, 'Five')
insert @myTable values (6, 'Six')
insert @myTable values (7, 'Seven')
insert @myTable values (8, 'Eight')
insert @myTable values (9, 'Nine')
insert @myTable values (10, 'Ten')

select * from @mytable

rownum      rowname
----------- ----------
1           One
2           Two
3           Three
4           Four
5           Five
6           Six
7           Seven
8           Eight
9           Nine
10          Ten

I need this output:

One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten

So far, this is the best code that I got:

declare @DelimitedString varchar(max)

SELECT
@DelimitedString = COALESCE(@DelimitedString+',' , '') + ColumnName 
FROM @MyTable

SELECT @DelimitedString

Here's the result set:

-------------------------------------------------
One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten

Then I tried parsing that string back to rows:

declare @xmldoc int
declare @DelimitedString varchar(250)

set @DelimitedString = 'One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten'

set @DelimitedString = '<root><z><y>' + replace(@DelimitedString, ',', '</y></z><z><y>') + '</y></z></root>'

select @DelimitedString

The variable now looks like this:

<root>
  <z>
    <y>One</y>
  </z>
  <z>
    <y>Two</y>
  </z>
  <z>
    <y>Three</y>
  </z>
  <z>
    <y>Four</y>
  </z>
  <z>
    <y>Five</y>
  </z>
  <z>
    <y>Six</y>
  </z>
  <z>
    <y>Seven</y>
  </z>
  <z>
    <y>Eight</y>
  </z>
  <z>
    <y>Nine</y>
  </z>
  <z>
    <y>Ten</y>
  </z>
</root>

Followed by...

exec sp_xml_preparedocument @xmldoc OUTPUT, @DelimitedString

select y as rows
from openxml(@xmldoc, '/root/z',2)
with (y varchar(15))

exec sp_xml_removedocument @xmldoc

Here's the result set:

rows
---------------
One
Two
Three
Four
Five
Six
Seven
Eight
Nine
Ten

As always, comments are welcome


~~ CK