I don't understand this behaviour

Question

I found this very useful function on Stackoverflow.

CREATE FUNCTION ToProperCase(@string VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @i INT           -- index
  DECLARE @l INT           -- input length
  DECLARE @c NCHAR(1)      -- current char
  DECLARE @f INT           -- first letter flag (1/0)
  DECLARE @o VARCHAR(255)  -- output string
  DECLARE @w VARCHAR(10)   -- characters considered as white space

  SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + ']'
  SET @i = 1
  SET @l = LEN(@string)
  SET @f = 1
  SET @o = ''

  WHILE @i <= @l
  BEGIN
    SET @c = SUBSTRING(@string, @i, 1)
    IF @f = 1 
    BEGIN
     SET @o = @o + @c
     SET @f = 0
    END
    ELSE
    BEGIN
     SET @o = @o + LOWER(@c)
    END

    IF @c LIKE @w SET @f = 1

    SET @i = @i + 1
  END

  RETURN @o
END

I wanted to include a few other characters to consider as white space so I added '-' and '/'.

When I had my code like this:

SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + '-' + '/' + ']'

the hyphen didn't work but everything else did.

I tried moving the hyphen before the space and when that didn't work, I moved it to the very front of the string after the first bracket where it did work.

I assume this has something to do with wildcards but I'm confused because I know underscore is a wildcard but why is hyphen behaving that way?

Answer 1

Try these adjustments:

SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + '\-' + '/' + ']'
. . .
IF @c LIKE @w escape '\' SET @f = 1

Answer 2

Hi @Nick Ryan (NZ) ,

Welcome to Microsoft Q&A!

As mentioned in LIKE ,the descrption of [] is any single character within the specified range ([a-f]) or set ([abcdef]).

At the original version, there is only blank inside [].

If you would like to add hyphen after blank, the SQL Server treat hyphen in a special way.

You could have a try to avoid this kind of issue by putting the hyphen at the very front after the first bracket or adding one '\' before hyphen.

If you would like to add a few characters, you could refer below and check whether it is helpful.

ALTER FUNCTION ToProperCase(@string VARCHAR(255)) RETURNS VARCHAR(255)  
 AS  
 BEGIN  
   DECLARE @i INT           -- index  
   DECLARE @l INT           -- input length  
   DECLARE @c NCHAR(1)      -- current char  
   DECLARE @f INT           -- first letter flag (1/0)  
   DECLARE @o VARCHAR(255)  -- output string  
   DECLARE @w VARCHAR(10)   -- characters considered as white space  
   DECLARE @w1 VARCHAR(10)   -- characters considered as white space  
   DECLARE @w2 VARCHAR(10)   -- characters considered as white space   
  
   SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9)+ ' ' + ']'  
   SET @w1 = '[' + CHAR(13) + CHAR(10) + CHAR(9)  + '-' +']'  
   SET @w2 = '[' + CHAR(13) + CHAR(10) + CHAR(9)   + '/' +']'  
   SET @i = 1  
   SET @l = LEN(@string)  
   SET @f = 1  
   SET @o = ''  
      
   WHILE @i <= @l  
   BEGIN  
     SET @c = SUBSTRING(@string, @i, 1)  
     IF @f = 1   
     BEGIN  
      SET @o = @o + @c  
      SET @f = 0  
     END  
     ELSE  
     BEGIN  
      SET @o = @o + LOWER(@c)  
     END  
      
     IF @c LIKE @w or @c LIKE @w1 OR @c LIKE @w2 SET @f = 1  
      
     SET @i = @i + 1  
   END  
      
   RETURN @o  
 END  

Then call this function as below:

select dbo.ToProperCase('ALL UPP-ER CA-SE A-ND SO/ME LOW/ER')  

Output:

All Upp-Er Ca-Se A-Nd So/Me Low/Er  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.