ASCII ProperCase Function

• Simon Hundleby

  SSCarpal Tunnel

  Points: 4566

  More actions

  July 17, 2017 at 1:27 pm

  #402314

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

• Robert Sterbal

  SSChampion

  Points: 11052

  More actions

  August 1, 2017 at 12:31 pm

  #1953651

  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: 3323

  More actions

  December 7, 2017 at 5:14 am

  #1971139

  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: 246970

  More actions

  December 7, 2017 at 5:41 am

  #1971145

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• KS

  Hall of Fame

  Points: 3323

  More actions

  December 7, 2017 at 10:03 am

  #1971207

  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: 186127

  More actions

  December 11, 2017 at 9:57 am

  #1971596

  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

• KS

  Hall of Fame

  Points: 3323

  More actions

  December 11, 2017 at 10:26 am

  #1971605

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

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

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

• Michael L John

  One Orange Chip

  Points: 27059

  More actions

  December 11, 2017 at 11:11 am

  #1971617

  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: 3323

  More actions

  December 12, 2017 at 3:09 am

  #1971707

  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: 27059

  More actions

  December 12, 2017 at 8:20 am

  #1971753

  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: 3323

  More actions

  December 12, 2017 at 8:34 am

  #1971761

  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.