September 15, 2011 at 6:23 pm
Hello Everyone
Hope that everything is going well. The weekend is almost here.
I am playing around with one of my own websites to change some things up a bit.
I would like to proper case each word in a string. This string is stored in a single column, as it is a title in a list table
Here is an example:
chocolate dipped strawberries mini cake
I would like this string to look like this:
Chocolate Dipped Strawberries Mini Cake
I have tried a couple things, but I just cannot seem to get there. I come close, but I am missing something. I know this has to be pretty simple.
I would greatly appreciate any help with this
Thank you in advance
Andrew SQLDBA
September 15, 2011 at 10:02 pm
There are several possible solutions here:
http://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case
I like the solution involving the sequence table and no looping.
CREATE FUNCTION String.InitCap( @string nvarchar(4000) ) RETURNS nvarchar(4000) AS
BEGIN
-- 1. Convert all letters to lower case
DECLARE @InitCap nvarchar(4000); SET @InitCap = Lower(@string);
-- 2. Using a Sequence, replace the letters that should be upper case with their upper case version
SELECT @InitCap = Stuff( @InitCap, n, 1, Upper( SubString( @InitCap, n, 1 ) ) )
FROM (
SELECT (1 + n1.n + n10.n + n100.n + n1000.n) AS n
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS n1
CROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS n10
CROSS JOIN (SELECT 0 AS n UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) AS n100
CROSS JOIN (SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000) AS n1000
) AS Sequence
WHERE
n BETWEEN 1 AND Len( @InitCap )
AND SubString( @InitCap, n, 1 ) LIKE '[a-z]' /* this character is a letter */
AND (
n = 1 /* this character is the first `character` */
OR SubString( @InitCap, n-1, 1 ) LIKE '[' + Char(32) + Char(9) + Char(13) + Char(10) + ']' /* the previous character is NOT a letter */
)
AND (
n < 3 /* only test the 3rd or greater characters for this exception */
OR SubString( @InitCap, n-2, 3 ) NOT LIKE '[a-z]''[a-z]' /* exception: The pattern <letter>'<letter> should not capatolize the letter following the apostrophy */
)
-- 3. Return the modified version of the input
RETURN @InitCap
END
edit: fixed copy paste mistake
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 10:29 pm
I am using currently this one. Though with loop but It's not slower for me:
DECLARE @InputString as VARCHAR(1000) ='chocolate dipped strawberries mini cake '
DECLARE @index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(1000)
SET @OutputString = LOWER(@InputString)
SET @index = 1
WHILE @index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @index, 1)
SET @PrevChar = CASE WHEN @index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @index, 1, UPPER(@Char))
END
END
SELECT @OutputString
Also, Jason's function needs a review as it's not doing the job (proper case).
Best Regards,
Sudhir
September 15, 2011 at 10:48 pm
Sudhir Dwivedi (9/15/2011)
Also, Jason's function needs a review as it's not doing the job (proper case).
Fixed - copy paste mistake.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2011 at 12:05 am
Jason:
Fixed - copy paste mistake.
Cool 🙂
September 16, 2011 at 1:29 am
DECLARE @STR VARCHAR(255) =' rajneeh
kumar
is
working in technext'
SET @STR = 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(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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F'), ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), ' l', ' L'), ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R'), ' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), ' x', ' X'), ' y', ' Y'), ' z', ' Z'), ' š', ' Š'), ' œ', ' Œ'), ' ž', ' Ž'), ' à', ' À'), ' á', ' Á'), ' â', ' Â'), ' ã', ' Ã'), ' ä', ' Ä'), ' å', ' Å'), ' æ', ' Æ'), ' ç', ' Ç'), ' è', ' È'), ' é', ' É'), ' ê', ' Ê'), ' ë', ' Ë'), ' ì', ' Ì'), ' í', ' Í'), ' î', ' Î'), ' ï', ' Ï'), ' ð', ' Ð'), ' ñ', ' Ñ'), ' ò', ' Ò'), ' ó', ' Ó'), ' ô', ' Ô'), ' õ', ' Õ'), ' ö', ' Ö'), ' ø', ' Ø'), ' ù', ' Ù'), ' ú', ' Ú'), ' û', ' Û'), ' ü', ' Ü'), ' ý', ' Ý'), ' þ', ' Þ'), ' ÿ', ' Ÿ')
SELECT RIGHT(@str, LEN(@str) - 1)
September 16, 2011 at 5:05 am
huh!!!!
DECLARE @STR VARCHAR(255) =' rajneeh
kumar
is
working in technext'
SET @STR = 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(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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F'), ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), ' l', ' L'), ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R'), ' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), ' x', ' X'), ' y', ' Y'), ' z', ' Z'), ' š', ' Š'), ' œ', ' Œ'), ' ž', ' Ž'), ' à', ' À'), ' á', ' Á'), ' â', ' Â'), ' ã', ' Ã'), ' ä', ' Ä'), ' å', ' Å'), ' æ', ' Æ'), ' ç', ' Ç'), ' è', ' È'), ' é', ' É'), ' ê', ' Ê'), ' ë', ' Ë'), ' ì', ' Ì'), ' í', ' Í'), ' î', ' Î'), ' ï', ' Ï'), ' ð', ' Ð'), ' ñ', ' Ñ'), ' ò', ' Ò'), ' ó', ' Ó'), ' ô', ' Ô'), ' õ', ' Õ'), ' ö', ' Ö'), ' ø', ' Ø'), ' ù', ' Ù'), ' ú', ' Ú'), ' û', ' Û'), ' ü', ' Ü'), ' ý', ' Ý'), ' þ', ' Þ'), ' ÿ', ' Ÿ')
SELECT RIGHT(@str, LEN(@str) - 1)
???:crazy:
September 16, 2011 at 5:25 am
The theory does actually work, it would just be useful if 5 seconds of care was taken before posting to make sure it was formatted correctly. If you just remove the carraige returns from the input string, it works. I would repost the SQL in working form, but my corporate network has a bizzaro firewall rule that tries to check for SQL Injection that prevents me from posting certain SQL text :hehe:
I agree it looks awful, but seem to remember that it's actually more efficient than it looks to have lots of nested replaces. In any case, it's outside the bounds of what's sensible to do in SQL - this is for a website - ASP .Net? .Net has much more appropriate methods to do this on the presentation layer where it belongs. E.g. the method ToTitleCase
September 16, 2011 at 7:13 am
Sudhir Dwivedi (9/16/2011)
huh!!!!
DECLARE @STR VARCHAR(255) =' rajneeh
kumar
is
working in technext'
SET @STR = 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(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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F'), ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), ' l', ' L'), ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R'), ' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), ' x', ' X'), ' y', ' Y'), ' z', ' Z'), ' š', ' Š'), ' œ', ' Œ'), ' ž', ' Ž'), ' à', ' À'), ' á', ' Á'), ' â', ' Â'), ' ã', ' Ã'), ' ä', ' Ä'), ' å', ' Å'), ' æ', ' Æ'), ' ç', ' Ç'), ' è', ' È'), ' é', ' É'), ' ê', ' Ê'), ' ë', ' Ë'), ' ì', ' Ì'), ' í', ' Í'), ' î', ' Î'), ' ï', ' Ï'), ' ð', ' Ð'), ' ñ', ' Ñ'), ' ò', ' Ò'), ' ó', ' Ó'), ' ô', ' Ô'), ' õ', ' Õ'), ' ö', ' Ö'), ' ø', ' Ø'), ' ù', ' Ù'), ' ú', ' Ú'), ' û', ' Û'), ' ü', ' Ü'), ' ý', ' Ý'), ' þ', ' Þ'), ' ÿ', ' Ÿ')
SELECT RIGHT(@str, LEN(@str) - 1)
???:crazy:
Try it like this: -
DECLARE @STR VARCHAR(255) ='rajneeh kumar is working in technext'
SET @STR = 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(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(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, ' a', ' A'),
' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F'),
' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'),
' l', ' L'), ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'),
' q', ' Q'), ' r', ' R'), ' s', ' S'), ' t', ' T'), ' u', ' U'),
' v', ' V'), ' w', ' W'), ' x', ' X'), ' y', ' Y'), ' z', ' Z'),
' š', ' Š'), ' œ', ' Œ'), ' ž', ' Ž'), ' à', ' À'), ' á', ' Á'),
' â', ' Â'), ' ã', ' Ã'), ' ä', ' Ä'), ' å', ' Å'), ' æ', ' Æ'),
' ç', ' Ç'), ' è', ' È'), ' é', ' É'), ' ê', ' Ê'), ' ë', ' Ë'),
' ì', ' Ì'), ' í', ' Í'), ' î', ' Î'), ' ï', ' Ï'), ' ð', ' Ð'),
' ñ', ' Ñ'), ' ò', ' Ò'), ' ó', ' Ó'), ' ô', ' Ô'), ' õ', ' Õ'),
' ö', ' Ö'), ' ø', ' Ø'), ' ù', ' Ù'), ' ú', ' Ú'), ' û', ' Û'),
' ü', ' Ü'), ' ý', ' Ý'), ' þ', ' Þ'), ' ÿ', ' Ÿ')
SELECT RIGHT(@str, LEN(@str) - 1)
It works by replacing any instance of a character with the capitalised version of that character.
Here's what I'd do (if I had to do it in SQL Server with no CLR): -
DECLARE @STR VARCHAR(255) ='this is a lovely test string'
SELECT STUFF((SELECT ' ' + UPPER(SUBSTRING(item,1,1))+LOWER(SUBSTRING(item,2,LEN(item)))
FROM dbo.DelimitedSplit8K(@str,' ') FOR XML PATH('')),1, 1, '')
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
September 16, 2011 at 7:37 am
HowardW (9/16/2011)
The theory does actually work, it would just be useful if 5 seconds of care was taken before posting to make sure it was formatted correctly. If you just remove the carraige returns from the input string, it works. I would repost the SQL in working form, but my corporate network has a bizzaro firewall rule that tries to check for SQL Injection that prevents me from posting certain SQL text :hehe:I agree it looks awful, but seem to remember that it's actually more efficient than it looks to have lots of nested replaces. In any case, it's outside the bounds of what's sensible to do in SQL - this is for a website - ASP .Net? .Net has much more appropriate methods to do this on the presentation layer where it belongs. E.g. the method ToTitleCase
My first inclination would be to have the OO language do the proper case. Still kinda fun to find a working SQL function.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2011 at 7:43 am
Cadavre (9/16/2011)
Sudhir Dwivedi (9/16/2011)
huh!!!!...
???:crazy:
Try it like this: -
DECLARE @STR VARCHAR(255) ='rajneeh kumar is working in technext'SET @STR = 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(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(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, ' a', ' A'),
' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F'),
' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'),
' l', ' L'), ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'),
' q', ' Q'), ' r', ' R'), ' s', ' S'), ' t', ' T'), ' u', ' U'),
' v', ' V'), ' w', ' W'), ' x', ' X'), ' y', ' Y'), ' z', ' Z'),
' š', ' Š'), ' œ', ' Œ'), ' ž', ' Ž'), ' à', ' À'), ' á', ' Á'),
' â', ' Â'), ' ã', ' Ã'), ' ä', ' Ä'), ' å', ' Å'), ' æ', ' Æ'),
' ç', ' Ç'), ' è', ' È'), ' é', ' É'), ' ê', ' Ê'), ' ë', ' Ë'),
' ì', ' Ì'), ' í', ' Í'), ' î', ' Î'), ' ï', ' Ï'), ' ð', ' Ð'),
' ñ', ' Ñ'), ' ò', ' Ò'), ' ó', ' Ó'), ' ô', ' Ô'), ' õ', ' Õ'),
' ö', ' Ö'), ' ø', ' Ø'), ' ù', ' Ù'), ' ú', ' Ú'), ' û', ' Û'),
' ü', ' Ü'), ' ý', ' Ý'), ' þ', ' Þ'), ' ÿ', ' Ÿ')
SELECT RIGHT(@str, LEN(@str) - 1)
It works by replacing any instance of a character with the capitalised version of that character.
Here's what I'd do (if I had to do it in SQL Server with no CLR): -
DECLARE @STR VARCHAR(255) ='this is a lovely test string'SELECT STUFF((SELECT ' ' + UPPER(SUBSTRING(item,1,1))+LOWER(SUBSTRING(item,2,LEN(item)))
FROM dbo.DelimitedSplit8K(@str,' ') FOR XML PATH('')),1, 1, '')
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
Though the "replace" method works - it has limitations. The biggest limitation is that one must go in and add all of the additional replaces for any characters not already there. That could get to be quite a pain real quick.
I like the implementation using the split function with upper and lower - good idea.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply