The function Len; maybe a solution

[JonyBlack]

Hi guys,

I'm not expert in SQL and my english is not perfect too, but I tried to find a solution about the Len problem. You know, if we try to get the Len of (' 123 '), we get 4 because all [space] on right side are trimmed. I did a little function to correct this bug and I would like to have your feedback and suggestion.

Thank you.

CREATE FUNCTION MyLen
(
@Text nvarchar(4000)
)
RETURNS int
AS
BEGIN
DECLARE @LoopDone bit,
@LenText int,
@Char nvarchar(1)

--************************************************** *************
-- This part count each char found in the text. That replace the function Len
-- because this one doesn't care the [space] on the right side.
SET @LoopDone = 1
SET @LenText = 0

WHILE @LoopDone = 1
BEGIN
SET @LenText = @LenText + 1

-- We read one char each time to count exactly how many characters it has.
SET @Char = SUBSTRING(@Text, @LenText, 1)

-- If @Char is NULL, then we have done.
IF ASCII(SUBSTRING(@Char, 1, 1)) IS NULL
BEGIN
-- Flag the loop to stop
SET @LoopDone = 0
-- We need to backward one char because the last position was in void.
SET @LenText = @LenText - 1
END
END
--************************************************** *************

RETURN @LenText
END