ASCII ProperCase Function

  • SimonH

    SSCarpal Tunnel

    Points: 4214

    Comments posted to this topic are about the item ASCII ProperCase Function

  • Robert Sterbal

    SSChampion

    Points: 10953

    When you submitted the function was there a place on the form to pick your license?

    412-977-3526 call/text

  • KS

    Hall of Fame

    Points: 3320

    Nicely done, but there are couple of drawbacks
    1. The function doesn't handle the non-alpha characters in the input string. See what happens if you use ' CONVERT tHis to 1 PROpper cASE' as input.
    2. Using a WHILE loop creates a RBAR and can be improved using a Tally table. Refer to the link here for more information.
    Below is an improved version of the function utilising ASCII

    IF OBJECT_ID('dbo.fnProperCase_V2') IS NOT NULL
     DROP FUNCTION dbo.fnProperCase_V2
    GO
    CREATE FUNCTION dbo.fnProperCase_V2
    (
     @InputString VARCHAR(2000)
    )
    RETURNS VARCHAR(2000) WITH SCHEMABINDING
    AS
    BEGIN
     SELECT @InputString = ' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' '
     ;WITH Tens (N) AS
     (
      SELECT N FROM
      (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS A (N)
     ),
     Tally (Num) AS
     (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
      FROM Tens AS A,Tens AS B,Tens AS C,Tens AS D
     )
     SELECT @InputString =
      STUFF(@InputString,Num + 1,1,CHAR(ASCII(SUBSTRING(@InputString,Num + 1,1))-32))
     FROM Tally
     WHERE Num < LEN(@InputString)
      AND SUBSTRING(@InputString,Num,1) = ' '
      AND ASCII(SUBSTRING(@InputString,Num + 1,1)) BETWEEN 97 AND 122
     RETURN(@InputString)
    END
    GO

  • Phil Parkin

    SSC Guru

    Points: 243544

    Sowbhari - Thursday, December 7, 2017 5:14 AM

    Nicely done, but there are couple of drawbacks
    1. The function doesn't handle the non-alpha characters in the input string. See what happens if you use ' CONVERT tHis to 1 PROpper cASE' as input.
    2. Using a WHILE loop creates a RBAR and can be improved using a Tally table. Refer to the link here for more information.
    Below is an improved version of the function utilising ASCII

    IF OBJECT_ID('dbo.fnProperCase_V2') IS NOT NULL
     DROP FUNCTION dbo.fnProperCase_V2
    GO
    CREATE FUNCTION dbo.fnProperCase_V2
    (
     @InputString VARCHAR(2000)
    )
    RETURNS VARCHAR(2000) WITH SCHEMABINDING
    AS
    BEGIN
     SELECT @InputString = ' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' '
     ;WITH Tens (N) AS
     (
      SELECT N FROM
      (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS A (N)
     ),
     Tally (Num) AS
     (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
      FROM Tens AS A,Tens AS B,Tens AS C,Tens AS D
     )
     SELECT @InputString =
      STUFF(@InputString,Num + 1,1,CHAR(ASCII(SUBSTRING(@InputString,Num + 1,1))-32))
     FROM Tally
     WHERE Num < LEN(@InputString)
      AND SUBSTRING(@InputString,Num,1) = ' '
      AND ASCII(SUBSTRING(@InputString,Num + 1,1)) BETWEEN 97 AND 122
     RETURN(@InputString)
    END
    GO

    Convert this to an iTVF and your performance gain will be greater still. Note also that the semicolon is a statement terminator, not a statement initiator.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • KS

    Hall of Fame

    Points: 3320

    Note also that the semicolon is a statement terminator, not a statement initiator.

    Noted, thank you.

    Convert this to an iTVF and your performance gain will be greater still.

    Came up with the below ITVF, but the performance is worse than the scalar function. Any suggestion on improving the ITVF or pointers where I'm doing wrong would be appreciated, there must be a better logic than below

    IF OBJECT_ID('dbo.fnProperCase_ITVF') IS NOT NULL
     DROP FUNCTION dbo.fnProperCase_ITVF
    GO
    CREATE FUNCTION dbo.fnProperCase_ITVF
    (
     @InputString VARCHAR(2000)
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    (
     WITH Tens (N) AS
     (
      SELECT N FROM
      (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS A (N)
     ),
     Tally (Num) AS
     (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
      FROM Tens AS A,Tens AS B,Tens AS C,Tens AS D
     ),
     Words (Num,Word) AS
     (
      SELECT
       Num,SUBSTRING(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num + 1,CHARINDEX(' ',' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num + 1) - Num -1)
      FROM Tally
      WHERE Num < LEN(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ')
       AND SUBSTRING(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num,1) = ' '
     )
     SELECT
      PropperString = STUFF((
       SELECT ' ' + UPPER(SUBSTRING(Word,1,1)) + SUBSTRING(Word,2,LEN(Word))
       FROM Words
       ORDER BY Num
       FOR XML PATH('')),1,1,'')
    )
    GO

    Attached is the test harness I used to compare the functions on a million row table and below are the timings.

    ================================================
    ========== Original Scalar Function ==========
    Duration = 00:01:21:760
    ================================================
    ========== Improved Scalar Function ==========
    Duration = 00:00:20:500
    ================================================
    ========== ITVF Function =====================
    Duration = 00:01:48:563
    ================================================

  • ChrisM@Work

    SSC Guru

    Points: 186043

    Here's an alternative which is worth testing:

    DECLARE @String VARCHAR(2000) = 'the quick brown fox'

    SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    ' ' + @String COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),

    ' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),

    ' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' a')

    https://connect.microsoft.com/SQLServer/feedback/details/512459/replace-function-extremely-slow-with-non-binary-windows-collation

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • KS

    Hall of Fame

    Points: 3320

    ChrisM@Work - Monday, December 11, 2017 9:57 AM

    Here's an alternative which is worth testing:

    DECLARE @String VARCHAR(2000) = 'the quick brown fox'

    SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    ' ' + @String COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),

    ' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),

    ' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')

    I have tested the above logic as Scalar & ITVF and it has good improvement. Below are the timings

    ================================================
    ========== Original Scalar Function ==========
    Duration = 00:01:20:077
    ================================================
    ========== Improved Scalar Function ==========
    Duration = 00:00:20:170
    ================================================
    ========== ITVF Function =====================
    Duration = 00:01:51:150
    ================================================
    ========== Chris Scalar Function =============
    Duration = 00:00:11:047
    ================================================
    ========== Chris ITVF Function ===============
    Duration = 00:00:08:300
    ================================================

    Thank you for the above link. Learnt something new today.

  • Michael L John

    One Orange Chip

    Points: 25717

    It also truncates the string if there are double spaces. 

    I used:
    'This Is a Test of this function'
    it returned
    'This Is A Test'

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • KS

    Hall of Fame

    Points: 3320

    Michael L John - Monday, December 11, 2017 11:11 AM

    It also truncates the string if there are double spaces. 

    I used:
    'This Is a Test of this function'
    it returned
    'This Is A Test'

    Could you please post the complete code you used as the above logic which Chris posted returns correctly, where the string has multiple spaces without any truncation.

  • Michael L John

    One Orange Chip

    Points: 25717

    Sowbhari - Tuesday, December 12, 2017 3:09 AM

    Michael L John - Monday, December 11, 2017 11:11 AM

    It also truncates the string if there are double spaces. 

    I used:
    'This Is a Test of this function'
    it returned
    'This Is A Test'

    Could you please post the complete code you used as the above logic which Chris posted returns correctly, where the string has multiple spaces without any truncation.


    DECLARE @String varchar(2000) = 'This Is a Test  of this function'
    SELECT dbo.fnProperCase(@String), @String

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • KS

    Hall of Fame

    Points: 3320

    Michael L John - Tuesday, December 12, 2017 8:20 AM

    Sowbhari - Tuesday, December 12, 2017 3:09 AM

    Michael L John - Monday, December 11, 2017 11:11 AM

    It also truncates the string if there are double spaces. 

    I used:
    'This Is a Test of this function'
    it returned
    'This Is A Test'

    Could you please post the complete code you used as the above logic which Chris posted returns correctly, where the string has multiple spaces without any truncation.


    DECLARE @String varchar(2000) = 'This Is a Test  of this function'
    SELECT dbo.fnProperCase(@String), @String

    "Results to Text" seems to be working fine but not "Results to Grid" in this scenario.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply