SQL Scalar-valued Function – Text to Proper Case / Title Case (Unicode proof)

I was looking for an easy to use function (udf) in SQL to convert any text to ‘Proper Case’ (also known as ‘Title Case’) and found Pinal’s blogpost:
http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/

Except, it wasn’t working properly for S’s or unicode strings…so I’ve added a few lines of code and here’s the result:

IMPORTANT: This UDF wasn’t build for performance, use it with care.
In my scenarios, I used it in my ETL processes just before inserting/updating the deltas/changes and thus minimizing the performance impact.

TextToProperCase - Unicode proof - Results

You’ve be able to download the T-SQL code here (just copy&paste the code into SSMS):
TextToProperCase – Unicode proof

Advertisements

4 thoughts on “SQL Scalar-valued Function – Text to Proper Case / Title Case (Unicode proof)

  1. Clint, I really liked what you did here. I decided it might work better as a table-valued function so it could be used with a CROSS APPLY. This will mostly get around the performance issues inherent in scalar functions. Also, I added an optional parameter that let’s you specify a string to convert to UPPER. Here’s the code. Hope you like it. If so perhaps you could post an update on LinkedIn. Regards, Steven Willis

    — =============================================
    — Author: Clint Huijbers
    — Create date: 2013-02-22
    — Modified by Steven Willis 2013-03-15 to be multi-statement tvf using tally table instead of loop
    — Description: This inline tvf casts a string variable to Proper Case (also known as Title Case).
    — =============================================

    ALTER FUNCTION dbo.tvfTextToProperCase
    (
    @InputString NVARCHAR(4000)
    ,@strVariations NVARCHAR(4000)
    )
    RETURNS
    @ProperCase TABLE
    (
    ItemID INT IDENTITY(1,1) NOT NULL,
    OutputString NVARCHAR(4000),
    PRIMARY KEY (ItemID)
    )
    AS
    BEGIN

    DECLARE
    @OutputString NVARCHAR(4000)
    ,@RowCount INT

    SET @InputString = LTRIM(RTRIM(@InputString))
    SET @strVariations = NULLIF(@strVariations,”)

    SET @OutputString = ”

    SELECT
    @OutputString = @OutputString +
    ISNULL(REPLACE(REPLACE(
    (CASE
    WHEN N = 1 OR NULLIF(SUBSTRING(@InputString,N-1,1),”) IS NULL
    THEN UPPER(SUBSTRING(@InputString,N,1))
    WHEN SUBSTRING(@InputString,N,1) IN (‘;’,’:’,’!’,’?’,’,’,’.’,’/’,’&’,””,’’’,'(‘,CHAR(9))
    THEN LOWER(SUBSTRING(@InputString,N,1))
    WHEN LOWER(SUBSTRING(@InputString,N-2,2)) = ‘mc’ AND SUBSTRING(@InputString,N-3,1) = ‘ ‘
    THEN UPPER(SUBSTRING(@InputString,N,1))
    WHEN LOWER(SUBSTRING(@InputString,N-3,3)) = ‘mac’ AND SUBSTRING(@InputString,N-4,1) = ‘ ‘
    THEN UPPER(SUBSTRING(@InputString,N,1))
    WHEN LOWER(SUBSTRING(@InputString,N-2,2)) IN (‘de’) AND SUBSTRING(@InputString,N-3,1) = ‘ ‘
    THEN UPPER(SUBSTRING(@InputString,N,1))
    WHEN (SUBSTRING(@InputString,N-1,1) IN (””,’’’)) AND (UPPER(SUBSTRING(@InputString,N,1))) IN (‘S’)
    THEN LOWER(SUBSTRING(@InputString,N,1))
    WHEN (SUBSTRING(@InputString,N-1,1) IN (””,’’’,’-‘,’/’)) AND (UPPER(SUBSTRING(@InputString,N,1))) NOT IN (‘S’)
    THEN UPPER(SUBSTRING(@InputString,N,1))
    ELSE
    LOWER(SUBSTRING(@InputString,N,1))
    END)
    ,’ ‘,’ ‘),CHAR(9),’ ‘),”)
    FROM
    dbo.Tally
    WHERE
    N 0
    AND t.N <= @RowCount

    END

    RETURN

    END
    GO

    /*
    Usage example:

    ;WITH cteNames
    AS (
    SELECT
    FullName
    FROM
    (VALUES
    ('john doe')
    ,('JAMES MCDONALD')
    ,('JAMES MACDONALD')
    ,('JAMES MACINTOSH')
    ,('JAMES PERMACTON')
    ,('JAMES DELANCY')
    ,('Brian McCall')
    ,('Howard D''Marque')
    ,('William O''Brian')
    ,('Frances Smith-Jones')
    ,('Patty Labelle')
    ,('Rock ''n'' Roll')
    ,('baby/child stuff')
    ,('THE MICHAEL''S')
    ,('ACE hardware')
    ,('A C E hardware')
    ,(' LEADING Spaces')
    ,('TRAILING Spaces ')
    ,('123 Main St')
    ,('TARPON SPRINGS')
    ,('USA Gallery')
    ,('O’NEILL’S FURN GALLERY')
    )
    AS Names (FullName)
    )
    SELECT
    pc.OutputString
    FROM
    cteNames cte
    CROSS APPLY
    dbo.tvfTextToProperCase(cte.FullName,NULL) AS pc
    –dbo.tvfTextToProperCase(cte.FullName,'USA |ACE | ROLL|TARPON') AS pc
    */

  2. Just in case you don’t have it, here’s Jeff Moden’s function DelimitedSplit8K which I use in the new version.

    CREATE FUNCTION [dbo].[DelimitedSplit8K]
    (
    @pString VARCHAR(8000)
    ,@pDelimiter CHAR(1)
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN

    WITH E1(N)
    AS (
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    UNION ALL
    SELECT
    1
    ), –10E+1 or 10 rows
    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 TOP (ISNULL(DATALENGTH(@pString),0))
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM E4
    ),
    cteStart(N1)
    AS (
    SELECT
    1
    UNION ALL
    SELECT
    t.N + 1
    FROM
    cteTally t
    WHERE
    SUBSTRING(@pString,t.N,1) = @pDelimiter
    ),
    cteLen(N1,L1)
    AS (
    SELECT
    s.N1
    ,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) – s.N1,8000)
    FROM
    cteStart s
    )

    SELECT
    ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)
    ,Item = SUBSTRING(@pString,l.N1,l.L1)
    FROM
    cteLen l ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s