Logo         Community
The Company
Your Account
Contact Us
Strings In MySQL
Use the powerful string processing toolkit in MySQL for common string manipulation tasks.

| String Theory |

In the MySQL world, the word "string" refers to a sequence of characters enclosed in quotes. Consider the following examples, all of which are valid MySQL string values:


"Bond 007"

"molly dook!"

Now, here's the twist: though string values are pretty easy to understand, putting them into a MySQL table is somewhat more involved. This because every table field that is to contain a string must be associated with a string data type, and MySQL supports not one, not two, but rather six different string data types, ranging from simple one-character string types to fields that support large blocks of text or binary data.

The simplest of them is the CHAR data type, primarily used for fixed-length strings. The length of the string that can be stored in a CHAR field ranges from 0 to 255 bytes and it must be specified at the time of field definition. An interesting characteristic of this field is that values smaller than the specified length will be right-padded with spaces, while values larger than the specified size will be automatically truncated. 

A close cousin (and more efficient variant) of the CHAR data type is the VARCHAR data type, which is useful for variable-length strings. The major difference between the two types is the manner in which data is stored: the VARCHAR type uses only the number of bytes actually needed to store the string. As a result, shorter values are not padded with spaces when inserted into a field declared as type VARCHAR.

For strings greater than 255 characters in length, MySQL provides the TEXT data type. Typically used to store large blocks of text, this data type does not require the user to specify any size. If the data inserted into the field exceeds the limits set by the system, the data simply gets truncated.

A variant of the TEXT data type is the BLOB data type, which is useful for storing binary data such as images or audio files. In reality, a BLOB data type is simply a TEXT type with the added feature of being compared and sorted in a case-sensitive manner (TEXT types are case-insensitive). Both TEXT and BLOB types also come with sub-variants, such as TINYTEXT and MEDIUMTEXT, which differ primarily in the amount of data they can hold.

Consider the following example, which demonstrates how to create fields of these types, as well as insert some data into them:

''.preg_replace(array('/  /', '/ /'), array('  ', '   '), '
mysql> CREATE TABLE strings (charField CHAR(3), varcharField VARCHAR(10), textField TEXT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO strings (charField, varcharField, textField) VALUES ('e', 'John Doe', 'It\'s a long way from Sunday today');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO strings (charField, varcharField, textField) VALUES ('abcdef', 'my@mail.net', 'So I said, "Come on, knave, and I\'ll show you who\'s better!"');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM strings;
| charField | varcharField | textField                                                    |
| e        | John Doe    | It's a long way from Sunday today                            |
| abc      | my@mail.ne  | So I said, "Come on, knave, and I'll show you who's better!" |
2 rows in set (0.01 sec)

Notice how, when a value entered into a CHAR field is larger than the allocated field size, it gets automatically truncated.

When dealing with a string value in MySQL, it's important to remember that string values must always be enclosed in single or double quotes, as in the example above. If your string value itself must contain a quote, you can always escape the symbol by preceding it with a backslash (the standard escape character in MySQL). The following example illustrates:

''.preg_replace(array('/  /', '/ /'), array('  ', '   '), '
mysql> INSERT INTO strings (textField) VALUES ('So I said, "Come on, knave, and I'll show you who's better!"');
ERROR 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 'll show you who's better!"')' at line 1

mysql> INSERT INTO strings (textField) VALUES ('So I said, "Come on, knave, and I\'ll show you who\'s better!"');
Query OK, 1 row affected (0.00 sec)

So, now you've got some strings in your database. How about doing something with them?

How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!

previous page more like this  print this article  next page
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
In cut!...
American Chai
The Core
Find out how you can use this article on your own Web site!

Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback