ASCII ProperCase Function

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

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

    412-977-3526 call/text

  • 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

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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
    ================================================

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • 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.

  • 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/

  • 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.

  • 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/

  • 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 10 (of 10 total)

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