July 17, 2017 at 1:27 pm
Comments posted to this topic are about the item ASCII ProperCase Function
August 1, 2017 at 12:31 pm
When you submitted the function was there a place on the form to pick your license?
412-977-3526 call/text
December 7, 2017 at 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
December 7, 2017 at 5:41 am
Sowbhari - Thursday, December 7, 2017 5:14 AMNicely 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
December 7, 2017 at 10:03 am
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
================================================
December 11, 2017 at 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')
https://connect.microsoft.com/SQLServer/feedback/details/512459/replace-function-extremely-slow-with-non-binary-windows-collation
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
December 11, 2017 at 10:26 am
ChrisM@Work - Monday, December 11, 2017 9:57 AMHere'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.
December 11, 2017 at 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'
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/
December 12, 2017 at 3:09 am
Michael L John - Monday, December 11, 2017 11:11 AMIt 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.
December 12, 2017 at 8:20 am
Sowbhari - Tuesday, December 12, 2017 3:09 AMMichael L John - Monday, December 11, 2017 11:11 AMIt 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/
December 12, 2017 at 8:34 am
Michael L John - Tuesday, December 12, 2017 8:20 AMSowbhari - Tuesday, December 12, 2017 3:09 AMMichael L John - Monday, December 11, 2017 11:11 AMIt 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