Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11

    Conversion in an INSERT query


    Hi there!

    I need to know how to have automatically the name "BROWN" (example) inserted this way: "Brown".

    The query is a classical and simple query:

    INSERT INTO `list`(`ID`, `lastName`, `firstName`, `school`, `year`) VALUES ([""],[BROWN],[JOHN],[Snowy Mountain],[1985])

    ID is AUTOINCREMENT

    Result should be:

    93, Brown, John, Snowy Mountain, 1985

    I need this tip because I have almost 3000 queries like this to execute and all the names are in caps!!!

    Many thanks in advance for your help!
    Last edited by Germaris; January 22nd, 2013 at 11:41 AM. Reason: SOLVED
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    mySQL does not have a title case function - by the way, are you using mySQL, those literals in [] in the VALUES list suggest otherwise!
    Where are these values stored, how are they created? If you cannot have the values created in the correct case you might be able to use UPPER and LOWER functions on the first character of the string and the rest, respectively.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11
    Originally Posted by SimonJM
    mySQL does not have a title case function - by the way, are you using mySQL, those literals in [] in the VALUES list suggest otherwise!
    Where are these values stored, how are they created? If you cannot have the values created in the correct case you might be able to use UPPER and LOWER functions on the first character of the string and the rest, respectively.
    Thanks for replying!

    Of course I'm using MySQL. What do you think?
    Inside phpMyAdmin 3.5.5, the template provided for INSERT is, for my given example table, what I posted
    This is a copy/paste from the SQL window. Nothing extraordinary here...

    These values are stored in a text file which I can modify with find and replace...

    But if there's no SQL Function for solving my problem, well, I think it's the end of the story...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    If you know C, it is easy to write a function like that. Then you install into your MySQL server and you can call it from SQL.

    You can also write a Trigger or a Stored Function to modify surname's value.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    You might be able to use a column or field-based tool such as awk, or any language that can use Regular Expressions to modify your text file - or rather a copy of your text file!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    36
    You could try the function in this post

    http://forums.devshed.com/showthread.php?p=1922166#post1922166
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    ... or you can use this one, which is better coded

    Code:
    DELIMITER ||
    DROP FUNCTION IF EXISTS uc_words;
    CREATE FUNCTION uc_words(str_in TEXT)
    	RETURNS TEXT
    	NO SQL
    	DETERMINISTIC
    	COMMENT 'Title Case. Assumes that there arent numbers or weird chars'
    BEGIN
    	-- loop counter
    	-- (change type if you expect input longer than 255 chars)
    	DECLARE i TINYINT UNSIGNED DEFAULT 1;
    	-- limit for i
    	DECLARE maxlen TINYINT UNSIGNED DEFAULT LENGTH(str_in) + 1;
    	-- current char
    	DECLARE ch CHAR(1) DEFAULT '';
    	-- output buffer
    	DECLARE str_out TEXT DEFAULT '';
    	-- a word is already started?
    	DECLARE in_word BOOL DEFAULT FALSE;
    	
    	-- loop chars
    	WHILE i < maxlen DO
    		-- get current char
    		SET ch = SUBSTRING(str_in FROM i FOR 1);
    		
    		IF ch REGEXP '[[:alpha:]]' THEN
    			-- letter
    			IF in_word IS TRUE THEN
    				-- word already started
    				SET str_out = CONCAT(str_out, LOWER(ch));
    			ELSE
    				-- word starts here
    				SET str_out = CONCAT(str_out, UPPER(ch));
    				SET in_word = TRUE;
    			END IF;
    		ELSE
    			-- no letter; add it and remember we're out of word
    			SET str_out = CONCAT(str_out, IF(ch, ch, ' '));
    			SET in_word = FALSE;
    		END IF;
    		
    		SET i = i + 1;
    	END WHILE;
    	
    	RETURN str_out;
    END;
    ||
    DELIMITER ;
    Results:

    MariaDB [test]> select uc_words('charlie brown');
    +---------------------------+
    | uc_words('charlie brown') |
    +---------------------------+
    | Charlie Brown |
    +---------------------------+
    1 row in set (0.00 sec)

    MariaDB [test]> select uc_words('lucy VAN PELT');
    +---------------------------+
    | uc_words('lucy VAN PELT') |
    +---------------------------+
    | Lucy Van Pelt |
    +---------------------------+
    1 row in set (0.00 sec)

    MariaDB [test]> select uc_words('LINUS');
    +-------------------+
    | uc_words('LINUS') |
    +-------------------+
    | Linus |
    +-------------------+
    1 row in set (0.00 sec)

    Comments on this post

    • Germaris agrees : Very good and efficient solution
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11
    Thank you very much for replying f_razzoli!

    But, sorry...
    I get this message:

    "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION uc_words(str_in TEXT) RETURNS TEXT NO SQL DETERMINISTIC ' at line 2"
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Germaris
    ...near 'CREATE FUNCTION uc_words(str_in TEXT) RETURNS TEXT NO SQL DETERMINISTIC ' at line 2"
    CREATE FUNCTION was supposed to be on line 3

    did you forget the DELIMITER?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11
    Originally Posted by r937
    CREATE FUNCTION was supposed to be on line 3

    did you forget the DELIMITER?
    Thanks for your remark.
    No, I didn't forget anything...
    ???
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    what version of mysql are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11
    Originally Posted by r937
    what version of mysql are you on?
    5.1.66

    I replaced str_in by the name of the column on which the function applies.
    Right?
    Last edited by Germaris; January 21st, 2013 at 03:10 PM.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    okay, that's good

    can i see the actual first three lines that you ran?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Pierrefonds, QC Canada
    Posts
    205
    Rep Power
    11
    Originally Posted by r937
    okay, that's good

    can i see the actual first three lines that you ran?
    DELIMITER ||
    DROP FUNCTION IF EXISTS uc_words;
    CREATE FUNCTION uc_words(lastName TEXT)
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    oh, wait... could this be the problem?

    try it like this --
    Code:
    DROP FUNCTION IF EXISTS uc_words;
    
    DELIMITER ||
    CREATE FUNCTION ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo