Init-cap the characters in SQL server
-
Hi Sir,
How to Init-cap the characters in SQL server in SQL query without creating any function?
create table abc
(Month_Name nvarchar(10))INSERT INTO abc VALUES ('APRIL');
INSERT INTO abc VALUES ('AUGUST');
INSERT INTO abc VALUES ('DECEMBER');
INSERT INTO abc VALUES ('FEBRUARY');
INSERT INTO abc VALUES ('JANUARY');
INSERT INTO abc VALUES ('JULY');
INSERT INTO abc VALUES ('JUNE');
INSERT INTO abc VALUES ('MARCH');
INSERT INTO abc VALUES ('MAY');
INSERT INTO abc VALUES ('NOVEMBER');
INSERT INTO abc VALUES ('OCTOBER');
INSERT INTO abc VALUES ('SEPTEMBER');My out put should show like below
April
August
December
February
January
July
June
March
May
November
October
September -
You could do this:
SELECT UPPER(LEFT(Month_Name,1)) + LOWER(SUBSTRING(Month_Name,2,8))
FROM abc - laurie-789651 - Friday, October 5, 2018 7:17 AM
This solves only the case for single word capitalization and only words up to 9 characters long, which is the case for month names, but perhaps little else. I thought I remembered seeing a post here sometime in the last 6 months that creates a function that delivers something like the functionality of the Proper() function from somewhere in my past... not sure if it was VBA or VB or Crystal Reports.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Friday, October 5, 2018 7:36 AM
Also note that the OP doesn't want to create a function. Not sure why, but that was the requirement.
- Lynn Pettis - Friday, October 5, 2018 7:40 AM
Yep... I know. Not usually practical without a function, at least for the general case. For this specific case, there's an easy way, but the number of such easy cases is probably pretty limited in scope.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
If your data is really just the twelve months of the year why not simply update the table? It would take about 2 minutes. But if that is just an example then use the string manipulation methods posted above.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This should be a function but the either the OP or the OP's DBA Team thinks they should be avoided because of some "Best Practice" someone found somewhere stating that functions should be avoided. Creating an iSF using a scalar return from an iTVF would be the way to go here.
In case someone reading this has no clue as to what I'm talking about, especially when it comes to performance, please see the following. The proper use of functions will greatly simplify life through the use of encapsulation.
How to Make Scalar UDFs Run Faster (SQL Spackle)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A utility table would be more efficient and if you are using a case insensitive collation all it would need is the actual list of proper cased months. If you must use a scalar function I would use CLR. The presentation layer is the best place to manipulate strings as you are typically presenting a small number of rows at a time so it's much faster.
- kiran.rajenimbalkar - Friday, October 5, 2018 2:32 AM
Shifting gears a bit, what are you actually going to use this for? Depending on where the data is coming from, what the data is a part of (in this case, is it a part of an actual date?), and what it's use is after the transformation, there may be better alternatives that just doing the initial caps thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, October 5, 2018 6:57 PM
Passing an interview?
_____________
Code for TallyGenerator - Sergiy - Monday, October 8, 2018 6:46 PM
Must be it. No one asked how to handle "MCDONALD" or "VAN DYCK". 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, October 8, 2018 8:49 PM
Apostrophes 😛
Far away is close at hand in the images of elsewhere.
Anon. - David Burrows - Tuesday, October 9, 2018 1:55 AM
And dashes and titles, too! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
USE JicSELECT a.Month_Name
GO
CREATE TABLE dbo.abc (Month_Name nvarchar(10) NOT NULL PRIMARY KEY)
GO
INSERT dbo.abc ( Month_Name )
VALUES (N'APRIL')
, (N'AUGUST')
, (N'DECEMBER')
, (N'FEBRUARY')
, (N'JANUARY')
, (N'JULY')
, (N'JUNE')
, (N'MARCH')
, (N'MAY')
, (N'NOVEMBER')
, (N'OCTOBER')
, (N'SEPTEMBER') ;
CREATE TABLE dbo.ProperMonths (ProperMonthName nvarchar(9) NOT NULL PRIMARY KEY);
GO
INSERT dbo.ProperMonths ( ProperMonthName )
VALUES ( N'April')
, ( N'August')
, ( N'December')
, ( N'February')
, ( N'January')
, ( N'July')
, ( N'June')
, ( N'March')
, ( N'May')
, ( N'November')
, ( N'October')
, ( N'September') ;
, p.ProperMonthName
FROM dbo.abc a
JOIN dbo.ProperMonths p ON a.Month_Name = p.ProperMonthName;
DROP TABLE dbo.abc;
DROP TABLE dbo.ProperMonths;
GO
- Joe Torre - Wednesday, October 10, 2018 1:29 PM
USE JicSELECT a.Month_Name
GO
CREATE TABLE dbo.abc (Month_Name nvarchar(10) NOT NULL PRIMARY KEY)
GO
INSERT dbo.abc ( Month_Name )
VALUES (N'APRIL')
, (N'AUGUST')
, (N'DECEMBER')
, (N'FEBRUARY')
, (N'JANUARY')
, (N'JULY')
, (N'JUNE')
, (N'MARCH')
, (N'MAY')
, (N'NOVEMBER')
, (N'OCTOBER')
, (N'SEPTEMBER') ;
CREATE TABLE dbo.ProperMonths (ProperMonthName nvarchar(9) NOT NULL PRIMARY KEY);
GO
INSERT dbo.ProperMonths ( ProperMonthName )
VALUES ( N'April')
, ( N'August')
, ( N'December')
, ( N'February')
, ( N'January')
, ( N'July')
, ( N'June')
, ( N'March')
, ( N'May')
, ( N'November')
, ( N'October')
, ( N'September') ;
, p.ProperMonthName
FROM dbo.abc a
JOIN dbo.ProperMonths p ON a.Month_Name = p.ProperMonthName;
DROP TABLE dbo.abc;
DROP TABLE dbo.ProperMonths;
GOAnd if your server or database is using a case sensitive collation?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply