MySQL - CHAR_LENGTH

UDEMY - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 들으면서 정리

MySQL String Function중에 하나인 CHAR_LENGTH

공식문서

글자수를 구해준다.

mysql> SELECT CHAR_LENGTH('Hello World');
+----------------------------+
| CHAR_LENGTH('Hello World') |
+----------------------------+
|                         11 |
+----------------------------+
1 row in set (0.00 sec)

먼저 작가들의 last name글자수들을 구해보자.

mysql> SELECT author_lname, CHAR_LENGTH(author_lname) FROM books;
+----------------+---------------------------+
| author_lname   | CHAR_LENGTH(author_lname) |
+----------------+---------------------------+
| Lahiri         |                         6 |
| Gaiman         |                         6 |
| Gaiman         |                         6 |
| Lahiri         |                         6 |
| Eggers         |                         6 |
| Eggers         |                         6 |
| Chabon         |                         6 |
| Smith          |                         5 |
| Eggers         |                         6 |
| Gaiman         |                         6 |
| Carver         |                         6 |
| Carver         |                         6 |
| DeLillo        |                         7 |
| Steinbeck      |                         9 |
| Foster Wallace |                        14 |
| Foster Wallace |                        14 |
+----------------+---------------------------+
16 rows in set (0.00 sec)

이렇게도 해줄 수 있다.

SELECT
	CONCAT(
		author_fname,' is ', CHAR_LENGTH(author_fname), 'characters long'
    )
FROM books;

'Jhumpa is 6characters long'
'Neil is 4characters long'
'Neil is 4characters long'
'Jhumpa is 6characters long'
'Dave is 4characters long'
'Dave is 4characters long'
'Michael is 7characters long'
'Patti is 5characters long'
'Dave is 4characters long'
'Neil is 4characters long'
'Raymond is 7characters long'
'Raymond is 7characters long'
'Don is 3characters long'
'John is 4characters long'
'David is 5characters long'
'David is 5characters long'

• 이런 식으로 활용해 줄 수도 있다. 위는 전체 작가들, 아래는 first name의 글자수가 4 이하인 작가들

mysql> SELECT author_fname from books;
+--------------+
| author_fname |
+--------------+
| Jhumpa       |
| Neil         |
| Neil         |
| Jhumpa       |
| Dave         |
| Dave         |
| Michael      |
| Patti        |
| Dave         |
| Neil         |
| Raymond      |
| Raymond      |
| Don          |
| John         |
| David        |
| David        |
+--------------+
16 rows in set (0.00 sec)

mysql> SELECT author_fname FROM books WHERE CHAR_LENGTH(author_fname) <= 4;
+--------------+
| author_fname |
+--------------+
| Neil         |
| Neil         |
| Dave         |
| Dave         |
| Dave         |
| Neil         |
| Don          |
| John         |
+--------------+
8 rows in set (0.00 sec)