SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ASCII ProperCase Function


ASCII ProperCase Function

Author
Message
SimonH
SimonH
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2999 Visits: 1088
Comments posted to this topic are about the item ASCII ProperCase Function
robert.sterbal 56890
robert.sterbal 56890
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4809 Visits: 1364
When you submitted the function was there a place on the form to pick your license?
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1809

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
Phil Parkin
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117621 Visits: 22472
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1809
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
================================================

Attachments
TestHarness.txt (10 views, 6.00 KB)
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88587 Visits: 20563
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
Exploring Recursive CTEs by Example Dwain Camps
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1809
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
Michael L John
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11373 Visits: 8885
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/
Sowbhari
Sowbhari
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1809
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
Michael L John
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11373 Visits: 8885
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search