Capitalize first letter of string with delimiter SQL Server-CodePudding

I have the following function to capitalize the first letter of a string, for example Company, so for a one word string with no space works fine with this function. For example, if I have SAHARA, it changes it to Sahara.

I need to use this function to capitalize the first letter of a string with a comma and firstname. For example, I have the following data and I am trying to accomplish Smith, John:

SMITH, JOHN
Smith, John
Sm ith, John

I have tried a few things in the CASE statements, but can't quite get it to work correctly, it will not capitalize the firstname of a string (for example Smith, John, the result is Smith, john).

What I have tried yields Smith, Jjohn, or Smith, JOhn

I would sure appreciate any help.

CREATE FUNCTION [dbo].[fnCapFirstLetterAndChangeDelim]
    (@string NVARCHAR(MAX), 
     @delimiter NCHAR(1), 
     @new_delimiter NCHAR(1))
RETURNS NVARCHAR(MAX)
AS 
BEGIN
    DECLARE @result NVARCHAR(MAX)

    SELECT @result = '';

    IF (LEN(@string) > 0)
        DECLARE @curr INT
        DECLARE @next INT
        BEGIN
            SELECT @curr = 1
            SELECT @next = CHARINDEX(@delimiter, @string)

            WHILE (LEN(@string) > 0)
            BEGIN
                SELECT @result = 
                        @result   
                        CASE WHEN LEN(@result) > 0 THEN @new_delimiter ELSE '' END  
                        UPPER(SUBSTRING(@string, @curr, 1))   
                        CASE 
                            WHEN @next <> 0 
                            THEN LOWER(SUBSTRING(@string, @curr 1, @next-2))
                            ELSE LOWER(SUBSTRING(@string, @curr 1, LEN(@string)-@curr))
                        END
                    IF (@next > 0)
                        BEGIN
                            SELECT @string = SUBSTRING(@string, @next 1, LEN(@string)-@next)
                            SELECT @next = CHARINDEX(@delimiter, @string)
                        END
                    ELSE
                        SELECT @string = ''
                END
        END
    RETURN @result
END

CodePudding user response：

Here is an inline table valued function you can use. It uses a tally table. In my case I keep a view name cteTally on hand for this kind of thing but any tally table will work.

ALTER function [dbo].[InitCap]
(
    @word varchar(2000)
) returns table

return
select camelcase = replace( word, '&#x20;', ' ' )
from (
   select case when N = 1 or substring( @word, N-1, 1 ) IN (' ', '-', '.') then upper( substring( @word, N, 1 ) )
                     else lower(substring( @word, N, 1 ) )
            end as [text()]
   from cteTally
   where N <= len( @word )
   for xml path( '' )
      ) as t( word )

And if you want to use my version of a tally table here is the view I keep around.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E 2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E 4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

CodePudding user response：

Perhaps a little more than needed, but it is easy to expand and customize

Example

Declare @YourTable Table ([SomeCol] varchar(500))
Insert Into @YourTable Values 
 ('SMITH, JOHN')
,('Smith, John')
,('Sm ith, John')
 
Select *
      ,NewVal = [dbo].[svf-Str-Proper](SomeCol)
 From @YourTable

Results

SomeCol         NewVal
SMITH, JOHN     Smith, John
Smith, John     Smith, John
Sm ith, John    Sm Ith, John

The SVF if interested

CREATE FUNCTION [dbo].[svf-Str-Proper] (@S varchar(max))
Returns varchar(max)
As
Begin
    Set @S = ' ' replace(replace(Lower(@S),'   ',' '),'  ',' ') ' '
    ;with cte1 as (Select * From (Values(' '),('-'),('/'),('\'),('['),('{'),('('),('.'),(','),('&'),(' Mc'),(' Mac'),(' O''') ) A(P))
         ,cte2 as (Select * From (Values('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M')
                                       ,('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
                                       ,('LLC'),('PhD'),('MD'),('DDS')
                                       ,('II '),('III '),('IV ')
                                       ,('ID '),('PW ')
                                 ) A(S))
         ,cte3 as (Select F = Lower(A.P B.S),T = A.P B.S From cte1 A Cross Join cte2 B 
                   Union All 
                   Select F = Lower(B.S A.P),T = B.S A.P From cte1 A Cross Join cte2 B where A.P in ('&') 
                  ) 
    Select @S = replace(@S,F,T) From cte3 
    Return rtrim(ltrim(@S))
End