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


ASCII ProperCase Function


ASCII ProperCase Function

Author
Message
SimonH
SimonH
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 1215
Comments posted to this topic are about the item ASCII ProperCase Function
robert.sterbal 56890
robert.sterbal 56890
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

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

Group: General Forum Members
Points: 2918 Visits: 2513

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 (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)SSC Guru (200K reputation)

Group: General Forum Members
Points: 200031 Visits: 23988
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.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2918 Visits: 2513
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 (35 views, 6.00 KB)
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)

Group: General Forum Members
Points: 157198 Visits: 21385
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.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2918 Visits: 2513
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19498 Visits: 9744
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.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2918 Visits: 2513
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19498 Visits: 9744
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