LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General > LinuxQuestions.org Member Success Stories
User Name
Password
LinuxQuestions.org Member Success Stories Just spent four hours configuring your favorite program? Just figured out a Linux problem that has been stumping you for months?
Post your Linux Success Stories here.

Notices


Reply
  Search this Thread
Old 02-17-2016, 10:30 AM   #1
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 20,713

Rep: Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091Reputation: 5091
MySQL/MariaDB Upper Casing letters/names in fields


Had a problem with a database (surprise!), that contained names. Names were entered by a variety of persons/programs, so we got a lump of stuff like:

DAVID
david
j. wilson
Franklin

...along with last names like:

VAN HORN
Johnson
wilson

None of which looked too good when it came time to print things out for them. While there is an easy method of plain upper-casing things, for things like "j. wilson" it didn't work right. It would upper-case the J, but not the W in "Wilson", same for last names.

Bang this in to your DB console:
Code:
DELIMITER ||  

CREATE FUNCTION `UC_Words`( str VARCHAR(255) ) RETURNS VARCHAR(255) 
BEGIN  
  DECLARE c CHAR(1);  
  DECLARE s VARCHAR(255);  
  DECLARE i INT DEFAULT 1;  
  DECLARE bool INT DEFAULT 1;  
  DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';  
  SET s = LCASE( str );  
  WHILE i < LENGTH( str ) DO  
     BEGIN  
       SET c = SUBSTRING( s, i, 1 );  
       IF LOCATE( c, punct ) > 0 THEN  
        SET bool = 1;  
      ELSEIF bool=1 THEN  
        BEGIN  
          IF c >= 'a' AND c <= 'z' THEN  
             BEGIN  
               SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));  
               SET bool = 0;  
             END;  
           ELSEIF c >= '0' AND c <= '9' THEN  
            SET bool = 0;  
          END IF;  
        END;  
      END IF;  
      SET i = i+1;  
    END;  
  END WHILE;  
  RETURN s;  
END ||  

DELIMITER ;
...and run it with something like "update table set firstname = UC_Words(firstname);"

And you'll wind up with "J. Wilson" and "Van Horn". Much nicer.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Text processing -- UPPER CASE doubled letters in second word of each line danielbmartin Programming 34 09-29-2014 07:55 AM
Question about creating files, and upper and lower case letters clifford227 Linux - Newbie 10 08-23-2012 06:02 AM
Need script for changing DIRECTORY names only to upper case PaxRomana Programming 15 05-08-2007 06:04 AM
file names, switching from upper to lower bhert Linux - Newbie 3 08-13-2006 10:43 PM
upper case letters in kde 3.5through tight vnc on debian sunpascal Linux - Software 0 03-28-2006 06:37 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - General > LinuxQuestions.org Member Success Stories

All times are GMT -5. The time now is 12:00 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration