Using String Functions in Your SQL Statements : String Function « String « SQL / MySQL

1. SQL / MySQL
2. String
3. String Function

Using String Functions in Your SQL Statements

/*
mysql> Drop table DVDs;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE DVDs (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(60) NOT NULL,
    ->    NumDisks TINYINT NOT NULL DEFAULT 1,
    ->    RatingID VARCHAR(4) NOT NULL,
    ->    StatID CHAR(3) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
    -> VALUES ('Christmas', 1, 'NR', 's1'),
    ->        ('Doc',       1, 'G',  's2'),
    ->        ('Africa',    1, 'PG', 's1'),
    ->        ('Falcon',    1, 'NR', 's2'),
    ->        ('Amadeus',   1, 'PG', 's2'),
    ->        ('Show',      2, 'NR', 's2'),
    ->        ('View',      1, 'NR', 's1'),
    ->        ('Mash',      2, 'R',  's2');
Query OK, 8 rows affected (0.10 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT Name, CHAR_LENGTH(Name) AS CharLength
    -> FROM DVDs
    -> WHERE CHAR_LENGTH(Name)>5
    -> ORDER BY Name;
+-----------+------------+
| Name      | CharLength |
+-----------+------------+
| Africa    |          6 |
| Amadeus   |          7 |
| Christmas |          9 |
| Falcon    |          6 |
+-----------+------------+
4 rows in set (0.05 sec)

*/

Drop table DVDs;

CREATE TABLE DVDs (
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(60) NOT NULL,
   NumDisks TINYINT NOT NULL DEFAULT 1,
   RatingID VARCHAR(4) NOT NULL,
   StatID CHAR(3) NOT NULL
)
ENGINE=INNODB;

INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES ('Christmas', 1, 'NR', 's1'),
       ('Doc',       1, 'G',  's2'),
       ('Africa',    1, 'PG', 's1'),
       ('Falcon',    1, 'NR', 's2'),
       ('Amadeus',   1, 'PG', 's2'),
       ('Show',      2, 'NR', 's2'),
       ('View',      1, 'NR', 's1'),
       ('Mash',      2, 'R',  's2');
  


SELECT Name, CHAR_LENGTH(Name) AS CharLength
FROM DVDs
WHERE CHAR_LENGTH(Name)>5
ORDER BY Name;

           
       

Related examples in the same category