July 9, 2008 at 5:04 am
I have a table with the name of states. I want the output to have
1>the first alphabet capital.
2>If it has only one or two characters then both capital.
3>If it has space between the characters then first character and the first character after gap should be capital.
create table test (A1 varchar(50))
insert into test select 'new york'
insert into test select 'californa'
insert into test select 'dc'
insert into test select 'new orleans'
insert into test select 'tennesse'
insert into test select 'miamae'
insert into test select 'or'
insert into test select 'knoxvilli'
insert into test select 'knoxvill i'
I want the output in this way.
New York
Californa
DC
New Orleans
Tennesse
Miamae
OR
Knoxvilli
Knoxvill I
I have designed the query but I am unable to get the 3rd condition rest all works fine.
select case when len(ltrim(rtrim(a1))) = 2 then upper(a1)
when len(ltrim(rtrim(a1))) > 2 and charindex(' ',ltrim(rtrim(a1))) = 0 then UPPER(SUBSTRING(a1,1,1))+ '' + lower(SUBSTRING(a1,2,len(a1)))
when charindex(' ',ltrim(rtrim(a1))) <> 0 then UPPER(SUBSTRING(ltrim(rtrim(a1)),1,1))+' '+
--UPPER(SUBSTRING(ltrim(rtrim(a1)),1,1))+''+ LOWER(SUBSTRING(a1,2,charindex(' ',ltrim(rtrim(a1))))-1) +''+
UPPER(SUBSTRING(ltrim(rtrim(a1)),charindex(' ',ltrim(rtrim(a1)))+1,1))+''+ lower(substring(a1,CHARINDEX(' ',ltrim(rtrim(a1)))+2,len(a1)))
else a1 end
from test
July 9, 2008 at 6:04 am
Hi Ashwin,
Try this...
select case when len(ltrim(rtrim(a1))) = 2 then
upper(a1)
when len(ltrim(rtrim(a1))) > 2 and charindex(' ',ltrim(rtrim(a1))) = 0 then
UPPER(SUBSTRING(a1,1,1))+ '' + lower(SUBSTRING(a1,2,len(a1)))
when charindex(' ',ltrim(rtrim(a1))) <> 0 then
UPPER(SUBSTRING(ltrim(rtrim(a1)),1,1))
+ lower(substring(a1,2,charindex(' ',a1)-1))
+ upper(substring(a1,charindex(' ',a1)+1,1))
+ lower(substring(a1,charindex(' ',a1)+2,len(a1) - charindex(' ',a1)))
else a1 end
from #test
Good luck!
July 9, 2008 at 7:19 am
Will you never have anything with more than one space or punctuation? For example, will you ever have something like "new york, ny"?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 7:27 am
Or will there possibly also be control characters in the result?
July 9, 2008 at 7:29 am
You gotta love English. I almost started to reply with "But you didn't include the States' Capitals." Then I realized that you didn't mean the capital capitals ("Capital"), but the non-capital capitals ("capital").
😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 9, 2008 at 7:32 am
HI All,
How about this?
SELECT
STUFF(CASE WHEN LEN(@vctemp)<=2
THEN UPPER(@vctemp)
ELSE UPPER(LEFT(@vctemp,1)) + LOWER(SUBSTRING(@vctemp,2,LEN(@vctemp)))
END,PATINDEX('% _%',@vctemp)+1,1,UPPER(SUBSTRING(@vctemp,PATINDEX('% _%',@vctemp) +1,1)))
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 9, 2008 at 7:37 am
mmm
Ok I have feeling that Jeffs question is going to lead to using a Numbers Table?!?!?
So you could please let us know the answer to that question it would be great.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 9, 2008 at 8:57 am
rbarryyoung (7/9/2008)
You gotta love English. I almost started to reply with "But you didn't include the States' Capitals." Then I realized that you didn't mean the capital capitals ("Capital"), but the non-capital capitals ("capital").😀
Heh... Capital idea that means you probably don't deserve capital punishment :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2008 at 8:29 am
I am personally trying to get some practice with a tally table, so here is what I came up with:
-- tNumber starts at 1
DECLARE @InString NVARCHAR(MAX)
SET @InString = 'this is a test of the emergency, broadcast-system.'
DECLARE @OutString NVARCHAR(MAX)
SELECT @OutString = ISNULL(@OutString, '') +
CASE
WHEN UPPER(CASE Number WHEN 1 THEN '' ELSE SUBSTRING( TestText, Number-1, 1) END) NOT LIKE '[0-Z]'
THEN UPPER(SUBSTRING( TestText, Number, 1))
ELSE SUBSTRING( TestText, Number, 1)
END
FROM
(SELECT 'this is a test of the emergency, broadcast-system.' AS TestText) A
JOIN tNumbers B
ON LEN(TestText) >= Number
PRINT @OutString
July 10, 2008 at 7:30 pm
Jeff Moden (7/9/2008)
Will you never have anything with more than one space or punctuation? For example, will you ever have something like "new york, ny"?
I'm still waiting for someone to answer the question above... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2008 at 7:54 pm
MentalWhiteNoise (7/10/2008)
I am personally trying to get some practice with a tally table, so here is what I came up with:
Nicely done... if I may suggest, let's simplify the code a bit by using one of the more arcane functions of SQL Server and moving some of the criteria of the CASE directly into the WHERE clause... Here's a function that I put into production for a similar problem...
[font="Courier New"]CREATE FUNCTION dbo.fProperCase (@MyString VARCHAR(8000))
/******************************************************************************
Purpose:
This function takes the input string, changes all characters to lower case,
and then changes the leading character of each word to uppercase.
Dependencies:
The dbo.Tally table must exist prior to use of this function.
Revision History:
10/23/2005 - Jeff Moden - Initial creation and unit test
******************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
--===== First, set the whole string to lowercase so we know the condition
SET @MyString = LOWER(@MyString)
--===== Set the first character to uppercase, no matter what
SET @MyString = STUFF(@MyString,1,1,UPPER(SUBSTRING(@MyString,1,1)))
--===== Set the first character following a "separator" to uppercase
SELECT @MyString = STUFF(@MyString,N+1,1,UPPER(SUBSTRING(@MyString,N+1,1)))
FROM dbo.Tally
WHERE N<LEN(@MyString)
AND SUBSTRING(@MyString,N,1) LIKE '[^A-Z]'
--===== Return the proper case value
RETURN @MySTRING
END[/font]
Here's one way to use it...
[font="Courier New"]
SELECT dbo.fProperCase('this is a tESt of the emERGENcy, broadcast-system 4yoU.')
AS ProperCased
ProperCased
--------------------------------------------------------
This Is A Test Of The Emergency, Broadcast-System 4You.
(1 row(s) affected)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2008 at 5:30 am
Nice! So it will only "process" for the places where the character in that place is not a character, versus mine where it uses a case on every character.
But what about the Join versus the Where? I keep trying to force myself to think in terms of sets (something that is still far from natural for me)... is there a large benefit to placing this logic in the where instead of a superficial (I say that because there is only one column in one of the tables) join?
Just curios.
Thanks.
July 11, 2008 at 8:46 am
First, let me suggest a minor correction to your code... First, try this... (your original code with a twist on the data)...
[font="Courier New"]DECLARE @OutString NVARCHAR(MAX)
SELECT @OutString = ISNULL(@OutString, '') +
CASE
WHEN UPPER(CASE Number WHEN 1 THEN '' ELSE SUBSTRING( TestText, Number-1, 1) END) NOT LIKE '[0-Z]'
THEN UPPER(SUBSTRING( TestText, Number, 1))
ELSE SUBSTRING( TestText, Number, 1)
END
FROM
(SELECT 'THIS IS A TEST OF THE EMERGENCY, BROADCAST-SYSTEM.' AS TestText) A
JOIN tNumbers B
ON LEN(TestText) >= Number
PRINT @OutString[/font]
See anything wrong with the output?
The correction is, as I said, very minor...
[font="Courier New"]DECLARE @OutString NVARCHAR(MAX)
SELECT @OutString = ISNULL(@OutString, '') +
CASE
WHEN UPPER(CASE Number WHEN 1 THEN '' ELSE SUBSTRING( TestText, Number-1, 1) END) NOT LIKE '[0-Z]'
THEN UPPER(SUBSTRING( TestText, Number, 1))
ELSE LOWER(SUBSTRING( TestText, Number, 1))
END
FROM
(SELECT 'THIS IS A TEST OF THE EMERGENCY, BROADCAST-SYSTEM.' AS TestText) A
JOIN tNumbers B
ON LEN(TestText) >= Number
PRINT @OutString[/font]
In actuality, both methods (your's and mine) have to "look" at every character and the STUFF function is relatively expensive compared to the fine concatenation method you used. All else being equal, one will beat the other depending on what the system is doing... they are both very close to each other in performance (each takes turns winning) and they're both identical in IO resources. So, to answer your question, there's no particular advantage to doing it one way or the other, in this case... here's the proof of that...
[font="Courier New"]--===== Do this in a "safe place"
USE TempDB
GO
--===== Create a Tally table here because we don't know where other's
-- Tally tables are...
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
GO
--===== Create MentalWhiteNoise' function with mods to use the current
-- Tally table and operate as a function.
CREATE FUNCTION dbo.MWNProperCase(@InString VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
--===== Declare local variables
DECLARE @OutString VARCHAR(8000)
--===== Capitalize first character following any non A-Z character
SELECT @OutString = ISNULL(@OutString, '') +
CASE
WHEN UPPER(CASE N
WHEN 1 THEN ''
ELSE SUBSTRING( TestText, N-1, 1) END) NOT LIKE '[0-Z]'
THEN UPPER(SUBSTRING( TestText, N, 1))
ELSE LOWER(SUBSTRING( TestText, N, 1))
END
FROM (SELECT @InString AS TestText) A
JOIN dbo.Tally B
ON LEN(TestText) >= N
--===== Return the modified string and exit
RETURN @OutString
END
GO
--===== Create the proper case function that Jeff Moden posted
CREATE FUNCTION dbo.JBMProperCase (@MyString VARCHAR(8000))
/******************************************************************************
Purpose:
This function takes the input string, changes all characters to lower case,
and then changes the leading character of each word to uppercase.
Dependencies:
The dbo.Tally table must exist prior to use of this function.
Revision History:
10/23/2005 - Jeff Moden - Initial creation and unit test
******************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
--===== First, set the whole string to lowercase so we know the condition
SET @MyString = LOWER(@MyString)
--===== Set the first character to uppercase, no matter what
SET @MyString = STUFF(@MyString,1,1,UPPER(SUBSTRING(@MyString,1,1)))
--===== Set the first character following a "separator" to uppercase
SELECT @MyString = STUFF(@MyString,N+1,1,UPPER(SUBSTRING(@MyString,N+1,1)))
FROM dbo.Tally
WHERE N<LEN(@MyString)
AND SUBSTRING(@MyString,N,1) LIKE '[^A-Z]'
--===== Return the proper case value
RETURN @MySTRING
END
GO
--===== Create a 10k row test table
SELECT TOP 10000
IDENTITY(INT,1,1) AS RowNum,
'THIS IS A TEST OF THE EMERGENCY, BROADCAST-SYSTEM 4YOU.' AS TestString
INTO dbo.JBMTest
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
GO
--==================================================================================
-- Compare performance and resource usage of both functions
--==================================================================================
--===== Declare a variable to put results instead of outputting to the screen to
-- ensure displaying to the screen does not affect the performance results
DECLARE @Bitbucket VARCHAR(8000)
--===== Test the two functions with certain stats turned on...
PRINT '========== MWN''s function ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Bitbucket = dbo.MWNProperCase(TestString)
FROM dbo.JBMTest
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
PRINT '========== Jeff''s function ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Bitbucket = dbo.JBMProperCase(TestString)
FROM dbo.JBMTest
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO 3
--===== Housekeeping
DROP FUNCTION dbo.MWNProperCase, dbo.JBMProperCase
DROP TABLE dbo.Tally, dbo.JBMTest
[/font]
All else being equal, it simply boils down to just one thing... which is easier for someone else to understand? Some will find your's easier and some will find that mine is. I'm banking on the "shorter" code, but I obviously could be wrong because, like I said earlier, the use of STUFF is a bit arcane for a lot of folks. Of course, a lot of folks don't understand how the rather arcane ISNULL concatenation method works, either. So, it all boils down to your personal preference on this one. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2010 at 6:50 pm
I know this is an older post but, if you're interested in performance, here's a new method I ran across. Yeah... I know. The RBAR method is actually faster than the Tally Table method because THIS RBAR method is all in memory. As good as it is, even a cached Tally Table can't quite keep up.
Almost forgot... here's the code I ran across. Kudos to George Mastros for the original concept.
CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))
/***************************************************************************************************
Purpose:
Capitalize any lower case alpha character which follows any non alpha character or single quote.
Revision History:
Rev 00 - 24 Feb 2010 - George Mastros - Initial concept
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function
Rev 01 - 25 Sep 2010 - Jeff Moden
- Redaction for personal use and added documentation.
- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered
- and the reduction of multiple SET statements to just 2 SELECT statements.
- Add no-cap single-quote by single-quote to the filter.
***************************************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
----------------------------------------------------------------------------------------------------
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
-- need to update. The collation here is essential to making this so simple.
-- A-z is equivalent to the slower A-Z
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
-- Note the reason for the speed here is that ONLY places that need capitalization
-- are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
----------------------------------------------------------------------------------------------------
RETURN @String;
END ;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply