SQLTeam.com | Weblogs | Forums

Covert All Caps to Proper Case

I need to convert all caps to proper case.
I have tblDataParcel containing the column OwnerName.
I need to go from JOHN SMITH to John Smith.
I found a Function but have not been able to get it to work.
Thank you for any assistance.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[initcap] (
    @string varchar(2000)
    )
RETURNS varchar(2000)
AS
--Author: Scott Pletcher
--Example: SELECT dbo.initcap('aBC mike;a alkj/as12k 43')
BEGIN

DECLARE @previousByte int
DECLARE @byte int

SET @string = LOWER(@string)
IF LEFT(@string, 1) LIKE '[a-z]'
    SET @string = STUFF(@string, 1, 1, UPPER(LEFT(@string, 1)))
SET @previousByte = 2

WHILE 1 = 1
BEGIN
    SET @byte = PATINDEX('%[^a-z0-9][a-z]%', SUBSTRING(@string, @previousByte, 2000))
    --PRINT 'Byte = ' + CAST(@byte AS varchar(10))
    IF @byte = 0
        BREAK
    SET @string = STUFF(@string, @previousByte + @byte, 1, 
        UPPER(SUBSTRING(@string, @previousByte + @byte, 1)))
    SET @previousByte = @previousByte + @byte + 1
    --PRINT 'PrevByte = ' + CAST(@previousByte AS varchar(10))
END --WHIILE

RETURN @string

END --FUNCTION
1 Like

Here is another version:

 ALTER Function dbo.TitleCase(@inputString varchar(8000))
Returns varchar(8000)
     As
  Begin
Declare @position int

 Select @inputString = stuff(lower(@inputString), 1, 1, upper(left(@inputString, 1)))
      , @position = patindex('%[^a-zA-Z][a-z]%', @inputString collate Latin1_General_Bin)

  While @position > 0
 Select @inputString = stuff(@inputString, @position, 2, upper(substring(@inputString, @position, 2)))
      , @position = patindex('%[^a-zA-Z][a-z]%', @inputString collate Latin1_General_Bin)

 Return @inputString
    End
GO

I also found a thing called SDU Tools which allows one to import such functions ready made.
I'm feeling really stupid - If I run these procedures in a query with the table open nothing happens. What am I doing wrong?

Can you show us what you are doing? Not sure what you mean by 'table open'...

You should have a query:

SELECT ..., dbo.TitleCase(a.column) As TitleCase
FROM yourtable a

Just want to change the all caps to proper case in OwnerName1.

image

create table bzoom100(OwnerName1 nvarchar(150))


insert into bzoom100
select upper(name) from sys.tables union
select N'ምድሪ' union
select upper('Geschäfts') union
select upper('Möglichkeit')


SELECT *, dbo.TitleCase(a.OwnerName1) As TitleCase
FROM bzoom100 a

odd when using collation Latin1_General_Bin

RÉDUISANT becomes RÉDuisant
but its good when using collation Latin1_General_100_CS_AS_SC

(just in case there are owner names with diacritic and all.

I am guessing that you right-clicked the table name and selected open (or edit)? You actually need to be in a new query window - click on 'New Query' in the toolbar to open that window.

Once opened - you can then write a query in that window to select the data from your table.

 Select dp.RefSubID
      , dp.SubIDType
      , dp.ParcelID
      , dp.OwnerName1
      , NewOwnerName = dbo.TitleCase(dp.OwnerName1)
   From dbo.tblDataParcel dp

If you want to update the data in the table and permanently change the values in the database, you have to issue an UPDATE statement. That can be done using the following:

Begin Transaction;  --start an explicit transaction so we can rollback the changes and verify

--==== Show the table before the changes
Select *
  From dbo.tblDataParcel dp;

--==== Update the table
Update dbo.tblDataParcel
   Set OwnerName1 = dbo.TitleCase(OwnerName1);

--==== Show the table after the changes
Select *
  From dbo.tblDataParcel dp;

--==== rollback or commit the changes
Rollback Transaction;  --leave this uncommented so you can review and validate
--Commit Transaction;  --uncomment this and comment the rollback when you want to make the changes permanent

You can execute the above in a query window - this will show you the table before the changes and after the changes. Once you are satisfied the changes are working as expected, uncomment the commit, comment out the rollback and execute. That will run the code and commit the changes to the database.

Another option would be to create a computed column for the table. You can add the computed column and use the function as the source for the column:

Alter Table dbo.tblDataParcel Add OwnerName1TitleCase As dbo.TitleCase(OwnerName1) PERSISTED;

Change the column name to whatever you want it to be - and now any time a new row is added to the table and someone enters a value in OwnerName1 it will automatically be converted and show up in your new column.

1 Like

This did it - Thank you!

Glad I could help - which option worked for you?

hi

i am looking at this topic
which is from 10 months ago !!!!!

i tried to do this using
user defined function !!!!

please click arrow to the left for FUNCTION Script
 -- Drop the function if it already exists
  IF OBJECT_ID('dbo.InitCap') IS NOT NULL
	DROP FUNCTION dbo.InitCap;
  GO
 
 -- Implementing Oracle INITCAP function
 CREATE FUNCTION dbo.InitCap (@inStr VARCHAR(8000))
  RETURNS VARCHAR(8000)
  AS
  BEGIN
    DECLARE @outStr VARCHAR(8000) = LOWER(@inStr),
		 @char CHAR(1),	
		 @alphanum BIT = 0,
		 @len INT = LEN(@inStr),
                 @pos INT = 1;		  
 
    -- Iterate through all characters in the input string
    WHILE @pos <= @len BEGIN
 
      -- Get the next character
      SET @char = SUBSTRING(@inStr, @pos, 1);
 
      -- If the position is first, or the previous characater is not alphanumeric
      -- convert the current character to upper case
      IF @pos = 1 OR @alphanum = 0
        SET @outStr = STUFF(@outStr, @pos, 1, UPPER(@char));
 
      SET @pos = @pos + 1;
 
      -- Define if the current character is non-alphanumeric
      IF ASCII(@char) <= 47 OR (ASCII(@char) BETWEEN 58 AND 64) OR
	  (ASCII(@char) BETWEEN 91 AND 96) OR (ASCII(@char) BETWEEN 123 AND 126)
	  SET @alphanum = 0;
      ELSE
	  SET @alphanum = 1;
 
    END
 
   RETURN @outStr;		   
  END
  GO
please click arrow to the left for DROP Create Data
drop table #data 
go 

create table #data 
(
name varchar(100)
)
go 

insert into #data select 'JOHN SMITH'
insert into #data select 'john smith'
go

image

SELECT  
    'SQL output '
	,name
	,dbo.InitCap(name) as Initialized_Capital 
FROM #data
go 

image

does not work on some locales

create table #data(OwnerName1 nvarchar(150))


insert into #data
select upper(name) from sys.tables union
select N'ምድሪ' union
select upper('Geschäfts') union
select upper('Möglichkeit') union
select upper('RÉDUISANT') union
select N'मुर्गी' union
select N'කුකුල් මස්' union
select N'ચિકન'


SELECT  
    'SQL output '
	,OwnerName1
	,dbo.InitCap(OwnerName1) as Initialized_Capital 
	--,dbo.TitleCase(OwnerName1) As TitleCase
FROM #data


drop table #data

hi yosiasz

Looks like i did not take LOCALES into account !!!

Oops !!!

:slight_smile:

imagine if this was an UPDATE statement on a big database that contained millions of rows with non Latin words. poof goes the data with ????? and poof goes your employment :laughing:

after poof goes your employment

Time to Start Selling
Lime Soda ..

And make millions of dollars
:slight_smile:
:slight_smile:

:joy::joy::joy::joy:Hey, I love anything lime. Or tamarind.