Proper Case for Column values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Site Related Discussions

Proper Case for Column values

Author

Topic

rdwilliamsjr
Starting Member

4 Posts

Posted - 2002-05-24 : 13:27:57

-- For testing
SELECT * FROM authors
UPDATE authors SET city = UPPER(city)

-- Replace spaces with the @ character
UPDATE pubs..authors SET city = REPLACE(city, ' ', '@')

-- Handle case 1 - First item
UPDATE pubs..authors
SET city = UPPER(SUBSTRING(LTRIM(city), 1, 1)) + LOWER(SUBSTRING(LTRIM(city), 2, 30))

-- Loop while there are rows with the flag
WHILE EXISTS(SELECT * FROM pubs..authors WHERE city LIKE '%@%')
BEGIN
-- Proper case the word after the flag.
UPDATE pubs..authors
SET city = SUBSTRING(city, 1, CHARINDEX('@', city)) +
UPPER(SUBSTRING(city, CHARINDEX('@', city) + 1, 1 )) +
LOWER(SUBSTRING(city, CHARINDEX('@', city) + 2, 30))
WHERE city LIKE '%@%'

-- Remove the first flag encountered in each row
UPDATE pubs..authors
SET city = SUBSTRING(city, 1, CHARINDEX( '@', city) - 1) +
' ' + SUBSTRING(city, CHARINDEX( '@', city) + 1, 30)
WHERE city LIKE '%@%'
END

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-05-26 : 10:32:09

I'm not sure I understand what this is and why it's in the forum. Can you give a little better description please.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources