SQLTeam.com | Weblogs | Forums

Capitalizing string


#1

Hi Guys,

I need to capitalize the city names like new york to New York,
here is what I have but only working on the first part not capitalizing the rest

SELECT Left(UPPER(city), 1)+Right(LOWER(city), len(city)+1)
from employee

image

Thanks!


#2

Hi

Please create function and use it

Does this help you
please let me know

Create Function

-- Create 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)
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

SET @Index = @Index + 1

END

RETURN @OutputString

END
GO

Query

SELECT [dbo].[InitCap] ( city ) AS City
from employee


#3

Using a CTE and FOR XML:
Used STUFF / FOR XML found on Stackoverflow:
Stackoverflow
XML split trick found on SQLauthority:
SQLauthority

IF OBJECT_ID('tempdb..#Cities') IS NOT NULL
DROP TABLE #Cities
CREATE TABLE #Cities
(
RowID INT IDENTITY(1,1) PRIMARY KEY
, CityName VARCHAR(50) DEFAULT ''
)

INSERT INTO #Cities values ('washington'),('new york'),('the big cities')

-- Devides city into words in CTE
;WITH CTE_cities (RowID,CityWords)
AS
(
SELECT RowID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS CityWords
FROM
(
SELECT c.RowID,CAST('' + REPLACE(c.CityName,' ','') + '' AS XML) AS x
FROM #Cities c
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
)

-- Selects and combines. Makes each cityword upper case.
SELECT cte2.RowID
, c.CityName
, CityCaps = STUFF(
(SELECT ' ' + UPPER(SUBSTRING(CityWords,1,1))+RIGHT(CityWords,LEN(CityWords)-1)
FROM CTE_cities cte1
WHERE cte1.RowID = cte2.RowID
FOR XML PATH (''))
, 1, 1, '') from CTE_cities cte2
INNER JOIN #Cities c on c.RowID=cte2.RowID -- Joining original table for select purposes.
GROUP BY cte2.RowID,c.CityName;


#4

Thanks Harish, I am trying to do this with a simple formula if I can. I am almost there but need to figure out the 2nd part. I can not add any functions to dB as I dont have any permissions.