SQL to change all CAPS to Caps

Question

Post reply

SQL to change all CAPS to Caps

isuckatsql

SSChampion

Points: 10381
More actions
March 11, 2010 at 9:28 am

#230763

Does anyone have the SQL code to change the contents of a table from all 'CAPS' to 'Caps'. First letter capital, then the rest of the word in lowercase.
Thanks
March 12, 2010 at 1:03 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

How many rows in how many columns in how many tables are you talking about?

If only a few rows in a few columns, I would UPDATE everything to all lower case, and then UPDATE it to Propercase.

Why is the front-end not handling this? This is part of the job of the front-end.

Andrew SQLDBA

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

oops

I forgot to include the code:

SELECT UPPER(LEFT(<ColumnName>, 1)) + LOWER(RIGHT(<ColumnName>,LEN(<ColumnName>)-1))

Andrew SQLDBA

davidandrews13 SSCertifiable Points: 6820 More actions · Answer 3

declare @table table(name varchar(10))

insert into @table

select 'CAPS'

select * from @table

update @table

set name = upper(SUBSTRING(name,1, 1)) + lower(SUBSTRING(name,2,len(name)))

select * from @table

something like this? or have i over complicated things?

isuckatsql SSChampion Points: 10381 More actions · Answer 4

The table is a list of all US Cities, and the person who created the table used all CAPS.

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

there are several script contributions of functions you can grab; look for "Propercase" in the scripts section;

that way you get "Raccoon City" instead of "Raccoon city" for multi-word locations.

here's one I have saved in my snippets, you would just do something like this:

UPDATE tbCity

Set CityName = dbo.Propercase(CityName)

the tally code:

CREATE FUNCTION ProperCase(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;with

a1 as (select 1 as N union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1),

a2 as (select 1 as N from a1 as a cross join a1 as b),

a3 as (select 1 as N from a2 as a cross join a2 as b),

a4 as (select 1 as N from a3 as a cross join a2 as b),

Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

--first char is always capitalized?

CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))

END

FROM dbo.Tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

GO

select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

isuckatsql SSChampion Points: 10381 More actions · Answer 6

isuckatsql

SSChampion

Points: 10381

March 11, 2010 at 6:15 pm

#1132414

Worked great!

Thanks.