I have a text which looks something like this VENDOR CORPORATION (GA/ATL). I want to make it look like Vendor Corporation (GA/ATL).
So, I want to make only the first letter of every word upper case except those words which exists between ( and ).
I came across - UPPER(LEFT(FIELD_NAME,1))+LOWER(SUBSTRING(FIELD_NAME,2,LEN(FIELD_NAME))), but it handles only one word at a time and doesn't have the functionality I want. A function which can do the job is most desired.
Try to use function like this:
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
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 != '(' AND @PrevChar != '/'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
IF @PrevChar = '('
SET @OutputString = LEFT(@OutputString, LEN(@OutputString) - LEN(SUBSTRING(@OutputString, CHARINDEX('(',@OutputString), CHARINDEX(')',@OutputString)))) + UPPER(SUBSTRING(@OutputString, CHARINDEX('(',@OutputString), CHARINDEX(')',@OutputString)))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
USAGE
SELECT [dbo].[InitCap]('VENDOR CORPORATION (GA/ATL)')
OUTPUT
Vendor Corporation (GA/ATL)
Using the Jeff Moden splitter (which can be found here. http://www.sqlservercentral.com/articles/Tally+Table/72993/) this can be accomplished. You then need to use a cross tab, also known as a conditional aggregate to put the piece back together. You could also do this with a PIVOT but I find the cross tab less obtuse for syntax and it has been proven to be slightly faster performance wise. This is also using the InitCap function found here. How to update data as upper case first letter with t-sql command?
declare @Value varchar(100) = 'VENDOR CORPORATION (GA/ATL)';
with sortedValues as
(
select Case when left(s.Item, 1) = '(' then s.Item else dbo.InitCap(s.Item) end as CorrectedVal
, s.ItemNumber
from dbo.DelimitedSplit8K(@Value, ' ') s
)
select MAX(case when ItemNumber = 1 then CorrectedVal end) + ' '
+ MAX(case when ItemNumber = 2 then CorrectedVal end) + ' '
+ MAX(case when ItemNumber = 3 then CorrectedVal end)
from sortedValues
If you don't know ahead of time how many "words" you will have you can adjust this crosstab to a dynamic version. You can read more about the dynamic crosstab here. http://www.sqlservercentral.com/articles/Crosstab/65048/
--EDIT--
Thanks to JamieD77 for a suggestion using STUFF. I particularly like this option because I have another version of InitCap that uses a tally table instead of the version referenced here which uses a while loop. Using STUFF facilitates turning this whole thing into an inline table valued function so it will be super fast. If anybody wants to see the InitCap without looping let me know and I will be happy to post it.
Here is the query using the suggested STUFF methodology.
SELECT STUFF((SELECT ' ' + s.CorrectedVal
FROM sortedValues s
ORDER BY s.ItemNumber
FOR
XML PATH('')
),1,1,'')
As an alternative to the above answers. If you want to just ignore what is between ( and ) you can use this slightly modified version of the InitCap function
CREATE FUNCTION [dbo].[InitCap] (
@InputString VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1) = ' '
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString,@Index,1)
IF @Char = '('
BEGIN
WHILE @Index <= LEN(@InputString)
AND @Char NOT IN (')')
BEGIN
SET @Index = @Index + 1
SET @PrevChar = @Char
SET @Char = SUBSTRING(@InputString,@Index,1)
SET @OutputString = STUFF(@OutputString,@Index,1,@Char)
END
END
IF @PrevChar IN (' ',';',':','!','?',',','.','_','-','/','&','''')
BEGIN
IF @PrevChar != ''''
OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString,@Index,1,UPPER(@Char))
END
SET @Index = @Index + 1
SET @PrevChar = @Char
END
RETURN @OutputString
END
GO
EXAMPLE
SELECT [dbo].[InitCap](n)
FROM ( VALUES ( 'VENDOR CORPORATION (GA/ATL)'),
( 'VENDOR CORPORATION (ga/atl)'),
( 'VENDOR CORPORATION (Ga/Atl)') ) t (n)
output
--------
Vendor Corporation (GA/ATL)
Vendor Corporation (ga/atl)
Vendor Corporation (Ga/Atl)
if you always want to CAPS everything inside ( and ) simply use SET @OutputString = STUFF(@OutputString,@Index,1,UPPER(@Char)) in both places
