Database entry update where caps are used

Question

I want to know if it's possible to change this:

ENTRY IN DATABASE WHERE CAPSLOCK IS USED. I WANT TO REMOVE THIS CAPS HERE.

To:

Entry in database where capslock is used. I want to remove this caps here.

With a script or anything withing sql. I have copied a database into another database, but alot of records contain fields with every character in capslock. Doing this by hand may take me some time since it contains like 50.000 rows +.

See http://stackoverflow.com/questions/1191605/is-there-a-simple-way-to-convert-mysql-data-into-title-case, that might work for you with small modifications. — Tatu Ulmanen, Jan 06 '10 at 15:29

John · Answer 1 · 2010-01-15T08:44:27.157

1

The feature you are looking for is known as you are proper case or sentence case.

You have not mentioned which database server (SQL Server, Oracle,MS Access etc.) however it is a feature most often not built in.

That said you should be able to create a custom functions no matter which system you are using. Here are some examples:

edited Jan 15 '10 at 08:44

answered Jan 06 '10 at 15:32

John

29,788
18
89
130

score 0 · Accepted Answer · answered Jan 06 '10 at 15:33

0

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';

   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end

select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')

From Simple T-SQL Proper Case User-Defined Function .

answered Jan 06 '10 at 15:33

Mitch Wheat

295,962
43
465
541

This solution is word for word identical to http://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case – NakedBrunch Jan 06 '10 at 15:47
@Alison: I have quoted the original source. – Mitch Wheat Jan 06 '10 at 15:49
Not excact what i was looking for, and i am not very good with sql :). I was looking for something that will just lowercase everything i have and only uses uppercase after a "." Well thanks anyway and i will try to get this to work for me. – Younes Jan 06 '10 at 15:49
Ah yes, I apologize. Until you pointed it out it looked like tags. Sorry +1. – NakedBrunch Jan 06 '10 at 15:50
That one definitely needs some work, as it capitalizes every word. I would replace the [a-zA-Z] with [a-zA-Z, ] for starters. It then doesn't deal with proper names correctly, but I think automating that would be pretty difficult. You would need to have a list of proper nouns to refer to as well as a way to know the context (for example, East is capitalized when referring to the location, but not the direction). That would be beyond simple SQL I think. – Tom H Jan 06 '10 at 16:02

score 0 · Answer 3 · answered Jan 06 '10 at 16:43

Oracle's INITCAP function won't do what's wanted here because it changes the first character of each word in the string to uppercase, all other characters are lowercased. Here's a PL/SQL function for Oracle which upcases the first character of a sentence and lowercases the rest:

CREATE OR REPLACE FUNCTION UPCASE_SENTENCES(strText IN VARCHAR2) RETURN VARCHAR2 IS
  strResult     VARCHAR2(32767);
  bUpcase_next  BOOLEAN := TRUE;
  i             NUMBER;
  thisC         VARCHAR2(1);

  FUNCTION isWhitespace(strC IN VARCHAR2) RETURN BOOLEAN IS
  BEGIN
    IF ASCII(strC) < 33 OR ASCII(strC) > 126 THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END isWhitespace;

BEGIN
  FOR i IN 1..LENGTH(strText) LOOP
    thisC := SUBSTR(strText, i, 1);

    IF bUpcase_next AND NOT isWhitespace(thisC) THEN
      strResult := strResult || UPPER(thisC);
      bUpcase_next := FALSE;
    ELSE
      strResult := strResult || LOWER(thisC);

      IF thisC IN ('.', '?', '!') THEN
        bUpcase_next := TRUE;
      END IF;
    END IF;
  END LOOP;

  RETURN strResult;
END UPCASE_SENTENCES;

I hope this helps.

Database entry update where caps are used

3 Answers3