question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked NickRyanNZ-4337 commented

I don't understand this behaviour

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?

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

the hyphen didn't work but everything else did.

Very unclear, what for a hyphen and what don't work?



0 Votes 0 ·

I am trying to change city/district names from all Upper Case to Proper Case. I have some like this:

SELECT Datacentral.dbo.ToProperCase( 'THAMES-COROMANDEL' )

So I want the result to be Thames-Coromandel, not Thames-coromandel.

The confusion probably came about because it would be obvious to most of you that I was misunderstanding the significance of the hyphen in the middle of the string. I was thinking it should just behave like any of the other characters I was searching for forgetting that it signifies a list of characters.

Using an escape character makes it behave like I anted it to.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered NickRyanNZ-4337 commented

Try these adjustments:

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This worked, perfectly, thank you. I didn't know about that use of the escape character.

0 Votes 0 ·

It seems that you did not choose to use LIKE ... ESCAPE.

0 Votes 0 ·

I did follow your suggestion. It was a neater way to make it work than the first suggestion although both work.

0 Votes 0 ·

I see, it appears one can only accept one answer. I've changed the Accepted Answer to yours as it is the one I followed.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered NickRyanNZ-4337 commented

Hi @NickRyanNZ-4337,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, thank you! My issue came from not fully thinking through how the sample code worked. I'd forgotten that the hyphen had special meaning in between the square brackets.

0 Votes 0 ·