I don't understand this behaviour

Nick Ryan (NZ) 121 Reputation points
2021-09-10T06:22:46.897+00:00

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?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

Accepted answer
  1. Viorel 114K Reputation points
    2021-09-10T07:37:32.57+00:00

    Try these adjustments:

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

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-09-10T07:26:24.22+00:00

    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.