TMACUL

MS SQL - Turn upper case for first letter/character on phrase

Blog Post created by TMACUL Champion on Sep 16, 2015

On projects sometimes is need optimizations (custom), normally is received information a little different would must be, can be duplicate, blank or simply formatted on UPPER CASE and for this case we can turn it to lower using this MS SQL function (oracle native).


Example:

UPPER CASE  ==(turn)==> Upper Case

TIAGO MACUL ==(turn)==> Tiago Macul

 

 

Code

alter FUNCTION dbo.[InitCap] ( @InputString varchar(8000) )

RETURNS VARCHAR(8000)

AS

BEGIN

 

 

DECLARE @counter          INT

DECLARE @CharTemp          CHAR(1)

DECLARE @PrevChar      CHAR(1)

DECLARE @OutputString  VARCHAR(255)

 

 

SET @OutputString = LOWER(@InputString) --left everthing lower

SET @counter = 1 --starting counter

 

 

--loop one by one characters, do it until max lenght from input string

WHILE @counter <= LEN(@InputString)

BEGIN

  --loading temporary variable

    SET @CharTemp    = SUBSTRING(@InputString, @counter, 1)

 

    --previous character, if first it´s empty

    SET @PrevChar = CASE WHEN @counter = 1 THEN ' '

                        ELSE SUBSTRING(@InputString, @counter - 1, 1)

                    END

 

 

  --looking for special characters where cannot be upper

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')

    BEGIN

        IF @PrevChar != '''' OR UPPER(@CharTemp) != 'S'

  --STUFF function inserts a string into another string, it´ll replace first upper

            SET @OutputString = STUFF(@OutputString, @counter, 1, UPPER(@CharTemp))

    END

 

 

    SET @counter = @counter + 1

END

 

 

RETURN @OutputString

 

 

END

GO

 

The first thing what you need know: VERY Important! Using pdm_load can be destructive so always back up your database before you perform a pdm_load, and use pdm_userload unless instructed to use pdm_load.

 

Important! Make a backup copy of the database before running pdm_load with this option. After old database records are removed, you must restore the CA SDM database with this backup copy if you want to recover any deleted records, use pdm_extract for this procedure.


Looking for results

select last_name, dbo.InitCap(last_name)

from mdb..ca_contact

 


How to use it:

1.

select '{"' + userid + '","' +  dbo.InitCap(last_name)  + '"}'

from mdb..ca_contact

where userid is not null

 

build one text archive inserting on top - mycontacts.txt

TABLE ca_contact

userid last_name

 

pdm_load -u -f mycontacts.txt

 

 

2.

update  mdb..ca_contact

set last_name =dbo.InitCap(last_name)

Outcomes