How To INIT CAP Data In T-SQL



TODO:

Have you ever wanted to convert the data in a column to INIT CAP?  For example you have data 'TEST DATA' and you want to change it to 'Test Data'?

 

SOLUTION:

 

CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @i		INT
,  @cWork		NVARCHAR(4000)

SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))

SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,  @cWork COLLATE Latin1_General_BIN)

WHILE 0 < @i
   BEGIN
      SET @cWork = Left(@cWork, @i) 
+        Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
      SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,        @cWork COLLATE Latin1_General_BIN)
   END

RETURN @cWork
END
GO

 

NOTES:

This solution was posted by user Pat Phelan on this site. 

To run issue this command:  update MyTable set Mycolumn= dbo.fCapFirst(Mycolumn)



Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading