May 14, 2020 at 7:43 pm
Jeff, quit torturing the poor guy and just give him the link
http://www.sqlservercentral.com/Forums/Topic530630-8-2.aspx
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 14, 2020 at 8:45 pm
Interesting. Does it correctly handle names like MacDonald, McDonald, van Halen, O'Leary, and things like "This is a Book"?
I think it's pretty basic. iirc "select initcap('This is a Book') from dual" would result in "This Is A Book"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 14, 2020 at 8:56 pm
Jeff, quit torturing the poor guy and just give him the link
Nice. Is there a unicode version?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 15, 2020 at 1:10 am
jonathan.crawford wrote:Jeff, quit torturing the poor guy and just give him the linkhttp://www.sqlservercentral.com/Forums/Topic530630-8-2.aspx
Nice. Is there a unicode version?
No, but the conversion should be fairly simple.
Man... I can't believe how time flies. I wrote that first version 15 years ago.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2020 at 9:42 am
Interesting. Does it correctly handle names like MacDonald, McDonald, van Halen, O'Leary, and things like "This is a Book"?
This is a limited attempt at this requirement. It does not meet the things like "This is a Book", which becomes "This Is A Book", and if your name is Macy Grey then all bets are off...
USE SystemMetaData;
GO
/****** Object: UserDefinedFunction [dbo].[fnProperCaseDJ] Script Date: 15/05/2020 09:15:50 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION dbo.fnProperCaseDJ (@InputString VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
/*
Select dbo.fnProperCaseDJ('the quiCk brown fox etc.')
Select dbo.fnProperCaseDJ('conner mccloud')
Select dbo.fnProperCaseDJ('ronald macdonald')
Select dbo.fnProperCaseDJ('brian van dyke') --Incorrect
Select dbo.fnProperCaseDJ('van dyke') --Correct if Surname only passed
Select dbo.fnProperCaseDJ('van der valke') --Incorrect
Select dbo.fnProperCaseDJ('der valke') --Correct if Surname only passed
Select dbo.fnProperCaseDJ('n''golo kante')
Select dbo.fnProperCaseDJ('patrick o''brian')
Select dbo.fnProperCaseDJ('n''golo o''brian')
Select dbo.fnProperCaseDJ('Macy Gray') --wildly wrong
*/
BEGIN
SET @InputString = LTRIM(RTRIM(LOWER(@InputString))); --Make everything lower case
SET @InputString = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(LOWER(@InputString))), ' ', '<>'), '><', ''), '<>', ' '); --remove multiple spaces
DECLARE @Returnvalue VARCHAR(2000) = '';
WITH E1 (N)
AS (SELECT 1
FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT (N) ) --10 rows
, E2 (N)
AS (SELECT 1 FROM E1 A CROSS JOIN E1 B) --100 rows
, E4 (N)
AS (SELECT TOP (2000) 1 FROM E2 A CROSS JOIN E2 B) --2,000 rows
, tally
AS (SELECT ROW_NUMBER() OVER (ORDER BY n.N) N FROM E4 AS n)
--Set based using a numbers table
SELECT @Returnvalue += CASE
WHEN t.N
= 1
OR ASCII(SUBSTRING(@InputString, LAG(t.N) OVER (ORDER BY t.N), 1)) = 32 --if previous Char is a space the make UPPERCASE
THEN CHAR(ASCII(SUBSTRING(@InputString, t.N, 1)) - 32)
ELSE SUBSTRING(@InputString, t.N, 1)
END
FROM
dbo.Tally t
WHERE
t.N <= LEN(@InputString);
-- Special Handling for Prefixes --------------------------------------------------------------
SET @Returnvalue
= CASE
WHEN @Returnvalue LIKE 'van %'
OR @Returnvalue LIKE 'der %' THEN
LOWER(SUBSTRING(@Returnvalue, 1, 4)) + UPPER(SUBSTRING(@Returnvalue, 5, 1))
+ SUBSTRING(@Returnvalue, 6, LEN(@Returnvalue))
WHEN @Returnvalue LIKE 'Mac%' THEN
SUBSTRING(@Returnvalue, 1, 3) + UPPER(SUBSTRING(@Returnvalue, 4, 1))
+ SUBSTRING(@Returnvalue, 5, LEN(@Returnvalue))
WHEN @Returnvalue LIKE 'Mc%'
OR @Returnvalue LIKE 'O''%'
OR @Returnvalue LIKE 'N''%' THEN
SUBSTRING(@Returnvalue, 1, 2) + UPPER(SUBSTRING(@Returnvalue, 3, 1))
+ SUBSTRING(@Returnvalue, 4, LEN(@Returnvalue))
ELSE @Returnvalue
END;
-- Special handing for hyphenated names -------------------------------------------------------
SET @Returnvalue
= CASE
WHEN @Returnvalue LIKE '%-%' THEN
SUBSTRING(@Returnvalue, 1, CHARINDEX('-', @Returnvalue))
+ UPPER(SUBSTRING(@Returnvalue, CHARINDEX('-', @Returnvalue) + 1, 1))
+ SUBSTRING(@Returnvalue, CHARINDEX('-', @Returnvalue) + 2, LEN(@Returnvalue))
ELSE @Returnvalue
END;
/*
-- Special handing for Mc, Mac and O' in the middle of a string -------------------------------------------------------
*/
SET @Returnvalue
= CASE
WHEN @Returnvalue LIKE '%O''%' THEN
SUBSTRING(@Returnvalue, 1, CHARINDEX('o''', @Returnvalue) + 1)
+ UPPER(SUBSTRING(@Returnvalue, CHARINDEX('o''', @Returnvalue) + 2, 1))
+ SUBSTRING(@Returnvalue, CHARINDEX('o''', @Returnvalue) + 3, LEN(@Returnvalue))
WHEN @Returnvalue LIKE '% Mc%' THEN
SUBSTRING(@Returnvalue, 1, CHARINDEX('Mc', @Returnvalue) + 1)
+ UPPER(SUBSTRING(@Returnvalue, CHARINDEX('Mc', @Returnvalue) + 2, 1))
+ SUBSTRING(@Returnvalue, CHARINDEX('Mc', @Returnvalue) + 3, LEN(@Returnvalue))
WHEN @Returnvalue LIKE '% Mac%' THEN
SUBSTRING(@Returnvalue, 1, CHARINDEX('Mac', @Returnvalue) + 2)
+ UPPER(SUBSTRING(@Returnvalue, CHARINDEX('Mac', @Returnvalue) + 3, 1))
+ SUBSTRING(@Returnvalue, CHARINDEX('Mac', @Returnvalue) + 4, LEN(@Returnvalue))
ELSE @Returnvalue
END;
RETURN @Returnvalue;
END;
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply