October 10, 2018 at 2:05 pm
USE Jic
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') ;
SELECT a.Month_Name
, p.ProperMonthName
FROM dbo.abc a
JOIN dbo.ProperMonths p ON a.Month_Name = p.ProperMonthName COLLATE Latin1_General_CI_AI;
DROP TABLE dbo.abc;
DROP TABLE dbo.ProperMonths;
GO
October 10, 2018 at 2:22 pm
bahhh... try and work with irish capitalization...
an tSín (China)
Máire Mhac an tSaoi "Mary McEntee"
where the capital is the Base word, and the first letter (eclipsis is how its called on Irish grammer) is lower case.
October 11, 2018 at 9:17 pm
Lynn Pettis - Wednesday, October 10, 2018 2:01 PMAnd if your server or database is using a case sensitive collation?
Run like hell? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2018 at 7:07 am
kiran.rajenimbalkar - Friday, October 5, 2018 2:32 AM...
Shifting gears a bit, what are you actually going to use this for? ...
Consider altering the table, using the month number as primary key. You don't want to use a function to get a proper name of the month.
-- Alter the dbo.abc table with Id.
declare @abc table (
Id int not null primary key,
Month_Name nvarchar(10) not null
);
-- Insert all month
set language US_English;
declare @i int = 1;
declare @MonthName nvarchar(10);
while @i <= 12
begin
select @MonthName = upper(datename(month , dateAdd(month, @i, -1)));
insert @abc(Id, Month_Name) values (@i, @MonthName);
set @i += 1;
end
-- Use a select to get the propername, in current language.
set language US_English;
select Id,
Month_Name,
dateName(month, dateAdd(month, Id, -1)) as ProperMonthName
from @abc
order by Month_Name asc;
October 15, 2018 at 7:24 am
Jeff Moden - Friday, October 5, 2018 6:57 PMkiran.rajenimbalkar - Friday, October 5, 2018 2:32 AM...
Shifting gears a bit, what are you actually going to use this for? ...
Consider altering the table, using the month number as primary key. You don't want to use a function to get a proper name of the month.
-- Alter the dbo.abc table with Id.
declare @abc table (
Id int not null primary key,
Month_Name nvarchar(10) not null
);
-- Insert all month
set language US_English;
declare @i int = 1;
declare @MonthName nvarchar(10);
while @i <= 12
begin
select @MonthName = upper(datename(month , dateAdd(month, @i, -1)));
insert @abc(Id, Month_Name) values (@i, @MonthName);
set @i += 1;
end
-- Use a select to get the propername, in current language.
set language US_English;
select Id,
Month_Name,
dateName(month, dateAdd(month, Id, -1)) as ProperMonthName
from @abc
order by Month_Name asc;
I guess I'd make either a permanent table or I'd use a Tally Function for this especially since the OP is using variables, which implies that there's already way too much RBAR in the process.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2018 at 11:52 am
Just as an alternative - and I hope no smart (....) copies this to another site.
select *
, stuff(upper(a.Month_Name), 2, 10, lower(substring(a.Month_Name, 2, 10)))
from abc a
but really all solutions presented offer very limited use as only useful to cap the first letter of a string, and lower the remaining. An attempt of doing proper title case of a string which does not work with all the exceptions that exist.
with high volumes, and taking just in consideration the limited functionality I would say the one that performs the best is the one that should be used
October 15, 2018 at 1:07 pm
Or you could just keep it simple. Use a table valued constructor to generate 12 rows and some basic date math. No need for init cap doing this. You could even join to to this quite easily by adding a second column of x.n to the output.
select datename(month, DATEADD(month, x.n - 1, ''))
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) x(n)
_______________________________________________________________
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/
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply