CODEWITHSUNDEEP
sql-server
Emma
Emma

Reputation: 13

Capitalise first letter of each word without changing currently Capitalised letters

I need to capitalise the first letter of each word but keep any capitals that may already be there in a textbox on submit to SQL Server. I currently have an SQL function that I call, but that Capitalises the first letter and lowercases the rest of each word. For example what I need is,

john smith - John Smith
ABC limited - ABC Limited

Below is the SQL function I currently have.

 ALTER FUNCTION [dbo].[CAP_FIRST] ( @InputString varchar(4000) ) 
 RETURNS VARCHAR(4000) AS BEGIN

 DECLARE @Index          INT DECLARE @Char           CHAR(1) DECLARE
 @PrevChar       CHAR(1) DECLARE @OutputString   VARCHAR(255)

 SET @OutputString = LOWER(@InputString) SET @Index = 1

 WHILE @Index <= LEN(@InputString) BEGIN
     SET @Char     = SUBSTRING(@InputString, @Index, 1)
     SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                          ELSE SUBSTRING(@InputString, @Index - 1, 1)
                     END

     IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
     BEGIN
         IF @PrevChar != '''' OR UPPER(@Char) != 'S'
             SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
     END

     SET @Index = @Index + 1 END

 RETURN @OutputString

 END

Upvotes: 1

Views: 112

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521427

It appears that the following line is responsible for lowercasing the entire input string:

SET @OutputString = LOWER(@InputString)

Then, the function selectively upper cases what it perceives to be the start of each word. Replace the above line with the following assignment and the original case of the input string should be preserved:

SET @OutputString = @InputString

Upvotes: 1

Related Questions