Database entry update where caps are used

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 d开发者_如何学JAVAatabase, 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 +.

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:

• SQL Server
• MYSQL
• MS Access

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 .

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.