T-SQL Function for string UpperCase

Possibly change
SET @Delimiter = ' ,-'
to
SET @Delimiter = ' ,-'''

I made the two changes
@Delimiter char(4)
SET @Delimiter = ' ,-''"'

I'm getting this error message

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Hara Mirror)
'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Hara Mirror)
'.

When I put in SELECT dbo.PROPERCASE(O'Hara Mirror)
or when I put in
SELECT dbo.PROPERCASE('O'Hara Mirror')


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Hara'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')
'.

You need to escape the ' character in O'Hara Mirror.

SELECT dbo.PROPERCASE('O''Hara Mirror')

I think the function suggested in the link below will handle this better.

http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/

I love you guys.. imnorie works great and ralmada I'm sure patels works great as well.. One more and I can put this in a new question if I need to.. What about a /? What is the data is O/hara Mirror

I would want O/Harra Mirror

Just change the declaration to use 5 instead of 4 and add / to the delimiter's string.

By the way, if you are going to add any more characters it might be worth looking at the function in the link ralmada posted.

I only had a brief look but I think it uses an array for the delimiters, which would be easier to change.

I thought that is what I needed to do but it didnt work.. See the h is not cap

ALTER FUNCTION [dbo].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
      IF @input IS NULL
      BEGIN
            --Just return NULL if input string is NULL
            RETURN NULL
      END
      
      --Character variable declarations
      DECLARE @output varchar(8000)
      --Integer variable declarations
      DECLARE @ctr int, @len int, @found_at int
      --Constant declarations
      DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(5), @UPPER_CASE_A int, @UPPER_CASE_Z int
      
      --Variable/Constant initializations
      SET @ctr = 1
      SET @len = LEN(@input)
      SET @output = ''
      SET @LOWER_CASE_a = 97
      SET @LOWER_CASE_z = 122
      --SET @Delimiter = ' ,-'
      SET @Delimiter = ' ,-''"/'



      SET @UPPER_CASE_A = 65
      SET @UPPER_CASE_Z = 90
      
      WHILE @ctr <= @len
      BEGIN
            --This loop will take care of reccuring white spaces
            WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
            BEGIN
                  SET @output = @output + SUBSTRING(@input,@ctr,1)
                  SET @ctr = @ctr + 1
            END

            IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
            BEGIN
                  --Converting the first character to upper case
                  SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
            END
            ELSE
            BEGIN
                  SET @output = @output + SUBSTRING(@input,@ctr,1)
            END
            
            SET @ctr = @ctr + 1

            WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
            BEGIN
                  IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
                  BEGIN
                  --added to handle exception.... A = 65, V = 86, L = 76, T = 84, C = 67, I = 73
                  if ascII(substring(@input,@ctr,1)) = 65 or ascII(substring(@input,@ctr,1)) = 86
                        begin
                              if ascII(substring(@input,@ctr - 1 ,1)) = 76 and substring(@input,@ctr + 1 ,1) = ' ' and substring(@input,@ctr -2 ,1) = ' ' or
                                     ascII(substring(@input,@ctr - 1 ,1)) = 84 and substring(@input,@ctr + 1 ,1) = ' ' or
                                     ascII(substring(@input,@ctr - 2 ,1)) <> 73 and ascII(substring(@input,@ctr - 1 ,1)) = 67 and substring(@input,@ctr + 1 ,1) = ' '
                                    begin
                                          SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
                                    end
                              Else
                              begin
                                    SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
                              end
                        end
                        
                  else
                        SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
                  END
                  ELSE
                  BEGIN
                        SET @output = @output + SUBSTRING(@input,@ctr,1)
                  END
                  SET @ctr = @ctr + 1
            END
            
      END
if @output = 'mar-a-lago'
            begin
                  set @output = 'Mar-a-Lago'
            end
if @output = 'LA Bella Chair'
      begin
            set @output = 'La Bella Chair'
      end
if @output = 'LA Bella Love Seat'
      begin
            set @output = 'La Bella Love Seat'
      end
if @output = 'LA Bella Ottoman'
      begin
            set @output = 'La Bella Ottoman'
      end
if @output = 'LA Bella Sofa'
      begin
            set @output = 'La Bella Sofa'
      end
if @output = 'LA Mancha Chair'
      begin
            set @output = 'La Mancha Chair'
      end
if @output = 'LA Palma Chair'
      begin
            set @output = 'La Palma Chair'
      end
if @output = 'BocA End Table'
      begin
            set @output = 'Boca End Table'
      end
      
RETURN @output
END

--SELECT dbo.PROPERCASE('O/hara Mirror')
 

>>What about a /? What is the data is O/hara Mirror <<

patel's function takes the / character into consideration. :)

I know, but THE BOSS wants to use the existing function...I know I know but I'm not the boss..

I need O/harra mirror to be O/Harra Mirror

You have too many 's in the delimiter string.

It should be this:

      SET @Delimiter = ' ,-''/'

Just a note, if you don't change the declaration then only the first X delimiters will be used, where X is the value used in the declaration.

eg if the declaration is char(3) only the first 3 characters will be recogninsed.