String
MySQL에서 제공하는 여러 문자열 함수를 사용하여 변환을 해보도록 하자.
- book_shop database 생성
CREATE DATABASE book_shop;
USE book_shop;이제 table을 만들어보도록 하자.
CREATE TABLE books
(
book_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
author_fname VARCHAR(100),
author_lname VARCHAR(100),
released_year INT,
stock_quantity INT,
pages INT,
PRIMARY KEY(book_id)
);
DESC books;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| book_id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | YES | | NULL | |
| author_fname | varchar(100) | YES | | NULL | |
| author_lname | varchar(100) | YES | | NULL | |
| released_year | int | YES | | NULL | |
| stock_quantity | int | YES | | NULL | |
| pages | int | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+book_id가 PK인 것을 알 수 있다.
이제 data를 넣어보도록 하자.
INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);
SELECT * FROM books;
+---------+-----------------------------------------------------+--------------+----------------+---------------+----------------+-------+
| book_id | title | author_fname | author_lname | released_year | stock_quantity | pages |
+---------+-----------------------------------------------------+--------------+----------------+---------------+----------------+-------+
| 1 | The Namesake | Jhumpa | Lahiri | 2003 | 32 | 291 |
| 2 | Norse Mythology | Neil | Gaiman | 2016 | 43 | 304 |
| 3 | American Gods | Neil | Gaiman | 2001 | 12 | 465 |
| 4 | Interpreter of Maladies | Jhumpa | Lahiri | 1996 | 97 | 198 |
| 5 | A Hologram for the King: A Novel | Dave | Eggers | 2012 | 154 | 352 |
| 6 | The Circle | Dave | Eggers | 2013 | 26 | 504 |
| 7 | The Amazing Adventures of Kavalier & Clay | Michael | Chabon | 2000 | 68 | 634 |
| 8 | Just Kids | Patti | Smith | 2010 | 55 | 304 |
| 9 | A Heartbreaking Work of Staggering Genius | Dave | Eggers | 2001 | 104 | 437 |
| 10 | Coraline | Neil | Gaiman | 2003 | 100 | 208 |
| 11 | What We Talk About When We Talk About Love: Stories | Raymond | Carver | 1981 | 23 | 176 |
| 12 | Where I'm Calling From: Selected Stories | Raymond | Carver | 1989 | 12 | 526 |
| 13 | White Noise | Don | DeLillo | 1985 | 49 | 320 |
| 14 | Cannery Row | John | Steinbeck | 1945 | 95 | 181 |
| 15 | Oblivion: Stories | David | Foster Wallace | 2004 | 172 | 329 |
| 16 | Consider the Lobster | David | Foster Wallace | 2005 | 92 | 343 |
+---------+-----------------------------------------------------+--------------+----------------+---------------+----------------+-------+sql 파일이 있다면 mysql shell에서 파일을 바로 로딩하여 sql 명령어들을 사용할 수 있다.
- book_data.sql
CREATE TABLE books
(
book_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
author_fname VARCHAR(100),
author_lname VARCHAR(100),
released_year INT,
stock_quantity INT,
pages INT,
PRIMARY KEY(book_id)
);
INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);위 파일의 경로에 가서 mysql에 접속한 후에 source book_data.sql을 실행하면 row들이 넣어진다.
CONCAT
문자열을 연결해주는 기능을 해준다.
SELECT CONCAT('h', 'e', 'l');
+-----------------------+
| CONCAT('h', 'e', 'l') |
+-----------------------+
| hel |
+-----------------------+해당 CONCAT 명령어를 이용하여 실제 데이터의 결과값을 바꿀 수 있다.
CONCAT(col1, col2);col1과 col2의 결과를 합쳐서 내어준다.
SELECT CONCAT(author_fname, '!!!') FROM books;
+-----------------------------+
| CONCAT(author_fname, '!!!') |
+-----------------------------+
| Jhumpa!!! |
| Neil!!! |
| Neil!!! |
| Jhumpa!!! |
| Dave!!! |
| Dave!!! |
| Michael!!! |
| Patti!!! |
| Dave!!! |
| Neil!!! |
| Raymond!!! |
| Raymond!!! |
| Don!!! |
| John!!! |
| David!!! |
| David!!! |
+-----------------------------+!!!가 붙은 것을 볼 수 있다.
이를 이용하여 first name과 last name을 붙일 수 있다.
SELECT CONCAT(author_fname,' ',author_lname) AS full_name FROM books;
+----------------------+
| full_name |
+----------------------+
| Jhumpa Lahiri |
| Neil Gaiman |
| Neil Gaiman |
| Jhumpa Lahiri |
| Dave Eggers |
| Dave Eggers |
| Michael Chabon |
| Patti Smith |
| Dave Eggers |
| Neil Gaiman |
| Raymond Carver |
| Raymond Carver |
| Don DeLillo |
| John Steinbeck |
| David Foster Wallace |
| David Foster Wallace |
+----------------------+작가의 full name이 찍히는 것을 볼 수 있다.
CONCAT말고도 CONCAT_WS가 있는데, WS는 white space를 의미한다. 이는 구분자를 각 단어 사이마다 넣어주는데, 다음과 같다.
CONCAT_WS(구분자, 문자, 문자 ...);구분자가 각 문자들을 concatenating할 때마다 붙는 것이다.
SELECT CONCAT_WS('!', 'hi', 'bye', 'lol');
+------------------------------------+
| CONCAT_WS('!', 'hi', 'bye', 'lol') |
+------------------------------------+
| hi!bye!lol |
+------------------------------------+!가 붙는 것을 볼 수 있다.
이를 활용하여 다음과 같이 만들 수 있다.
SELECT CONCAT_WS('-',title,author_fname, author_lname) FROM books;
+--------------------------------------------------------------------+
| CONCAT_WS('-',title, author_fname, author_lname) |
+--------------------------------------------------------------------+
| The Namesake-Jhumpa-Lahiri |
| Norse Mythology-Neil-Gaiman |
| American Gods-Neil-Gaiman |
| Interpreter of Maladies-Jhumpa-Lahiri |
| A Hologram for the King: A Novel-Dave-Eggers |
| The Circle-Dave-Eggers |
| The Amazing Adventures of Kavalier & Clay-Michael-Chabon |
| Just Kids-Patti-Smith |
| A Heartbreaking Work of Staggering Genius-Dave-Eggers |
| Coraline-Neil-Gaiman |
| What We Talk About When We Talk About Love: Stories-Raymond-Carver |
| Where I'm Calling From: Selected Stories-Raymond-Carver |
| White Noise-Don-DeLillo |
| Cannery Row-John-Steinbeck |
| Oblivion: Stories-David-Foster Wallace |
| Consider the Lobster-David-Foster Wallace |
+--------------------------------------------------------------------+SUBSTRING
긴 문자열을 취해서 작은 문자열로 반환하는 함수로, 큰 텍스트에서 일부를 가져오는 함수이다.
SELECT SUBSTRING(large_string, start, length);large_string에서 start부터 length까지를 가져오는 것이다. 단, 문자열의 인덱스는 1부터 시작한다. 만약, start만 쓰면 끝까지를 가져온다.
'Hello World'에서 Hello만 가져오고 싶다면 다음과 같이 쓸 수 있다.
SELECT SUBSTRING('Hello World', 1, 5);
+--------------------------------+
| SUBSTRING('Hello World', 1, 5) |
+--------------------------------+
| Hello |
+--------------------------------+start만 넣어주면 끝까지 substring을 가져온다.
SELECT SUBSTRING('Hello World', 3);
+-----------------------------+
| SUBSTRING('Hello World', 3) |
+-----------------------------+
| llo World |
+-----------------------------+start를 음수로 시작할 수도 있는데, 이는 뒤부터 숫자를 센다고 생각하면 된다.
가령, World만 가져오고 싶다면 뒤붙 5번째 이므로 다음과 같이 할 수 있다.
SELECT SUBSTRING('Hello World', -5);
+------------------------------+
| SUBSTRING('Hello World', -5) |
+------------------------------+
| World |
+------------------------------+column을 입력하여 특정 attribute에 대한 substring을 가져올 수도 있다.
가령 title column의 첫번째부터 15번째 까지의 글자를 가져온다고 하자.
SELECT SUBSTRING(title, 1, 15) FROM books;
+-------------------------+
| SUBSTRING(title, 1, 15) |
+-------------------------+
| The Namesake |
| Norse Mythology |
| American Gods |
| Interpreter of |
| A Hologram for |
| The Circle |
| The Amazing Adv |
| Just Kids |
| A Heartbreaking |
| Coraline |
| What We Talk Ab |
| Where I'm Calli |
| White Noise |
| Cannery Row |
| Oblivion: Stori |
| Consider the Lo |
+-------------------------+다음과 같이 쓸 수 있는 것이다.
SUBSTRING과 SUBSTR 함수는 동일한 기능을 하는 다른 이름의 함수이므로 서로 바꾸어써도 된다.
가령, last name의 첫번째 이름만 가져오고 싶다면 다음과 같이 할 수 있다.
CONCAT과 SUBSTR을 함께 조합하여 사용할 수 있다. 가령, title column이 너무 긴 경우 일부만 얻어오고, ...을 붙여서 처리할 수 있다.
SELECT CONCAT(SUBSTR(title, 1, 10), '...') AS short_title FROM books;
+---------------+
| short_title |
+---------------+
| The Namesa... |
| Norse Myth... |
| American G... |
| Interprete... |
| A Hologram... |
| The Circle... |
| The Amazin... |
| Just Kids... |
| A Heartbre... |
| Coraline... |
| What We Ta... |
| Where I'm ... |
| White Nois... |
| Cannery Ro... |
| Oblivion: ... |
| Consider t... |
+---------------+title을 줄였으니, 작가의 이름을 줄여서 표기해보도록 하자. 가령 J.K.drunker 같이 말이다.
SELECT CONCAT(
SUBSTR(author_fname, 1, 1), '.',SUBSTR(author_lname,1,1),'.'
) AS author_initials
FROM books;
+-----------------+
| author_initials |
+-----------------+
| J.L. |
| N.G. |
| N.G. |
| J.L. |
| D.E. |
| D.E. |
| M.C. |
| P.S. |
| D.E. |
| N.G. |
| R.C. |
| R.C. |
| D.D. |
| J.S. |
| D.F. |
| D.F. |
+-----------------+REPLACE
특정 문자를 다른 문자로 변경해줄 수 있다.
SELECT REPLACE(source, origin_str, replace_str);source 문자열에서 origin_str부분을 replace_str로 바꾼다는 의미이다.
SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com |
+-------------------------------------+w가 모두 Ww로 바뀐 것을 볼 수 있다.
다른 예제로 공백을 and로 바꿀 수도 있다.
SELECT REPLACE('cheese bread coffee milk', ' ', ' and ');
+---------------------------------------------------+
| REPLACE('cheese bread coffee milk', ' ', ' and ') |
+---------------------------------------------------+
| cheese and bread and coffee and milk |
+---------------------------------------------------+그러나, 한 가지 명심해야할 것은 대소문자에 민감하다는 것이다.
SELECT REPLACE('I am Colt!', 'colt', 'park');
+---------------------------------------+
| REPLACE('I am Colt!', 'colt', 'park') |
+---------------------------------------+
| I am Colt! |
+---------------------------------------+Colt가 안바뀌는 것을 볼 수 있다.
REVERSE
말 그대로 문자열을 거꾸로 바쒀준다.
SELECT REVERSE("HELLO WORLD");
+------------------------+
| REVERSE("HELLO WORLD") |
+------------------------+
| DLROW OLLEH |
+------------------------+재밌는 것은 NULL값을 넣으면 NULL만 나온다.
SELECT REVERSE(NULL);
+------------------------------+
| REVERSE(NULL) |
+------------------------------+
| NULL |
+------------------------------+CHAR_LENGTH
문자열의 길이를 알려준다.
SELECT CHAR_LENGTH('text');
+---------------------+
| CHAR_LENGTH('text') |
+---------------------+
| 4 |
+---------------------+LENGTH도 길이를 반환하지만 byte의 길이를 반환한다. 반면에 CHAR_LENGTH는 문자의 길이를 반환하기 때문에 일반적인 숫자나 alphabet은 차이가 없겠지만, 유니코드로 인코딩되는 한국어, 한자는 차이가 날 수 밖에 없다.
SELECT CHAR_LENGTH(title) AS title_len, title FROM books;
+-----------+-----------------------------------------------------+
| title_len | title |
+-----------+-----------------------------------------------------+
| 12 | The Namesake |
| 15 | Norse Mythology |
| 13 | American Gods |
| 23 | Interpreter of Maladies |
| 32 | A Hologram for the King: A Novel |
| 10 | The Circle |
| 41 | The Amazing Adventures of Kavalier & Clay |
| 9 | Just Kids |
| 41 | A Heartbreaking Work of Staggering Genius |
| 8 | Coraline |
| 51 | What We Talk About When We Talk About Love: Stories |
| 40 | Where I'm Calling From: Selected Stories |
| 11 | White Noise |
| 11 | Cannery Row |
| 17 | Oblivion: Stories |
| 20 | Consider the Lobster |
+-----------+-----------------------------------------------------+LOWER, UPPER
LOWER는 대문자를 소문자로, UPPER는 소문자를 대문자로 바꿔준다.
SELECT LOWER('HeJ');
+--------------+
| LOWER('HeJ') |
+--------------+
| hej |
+--------------+
SELECT UPPER('HeJ');
+--------------+
| UPPER('HeJ') |
+--------------+
| HEJ |
+--------------+title을 다음과 같이 대문자들로 만들 수 있다.
SELECT UPPER(title) FROM books;
+-----------------------------------------------------+
| UPPER(title) |
+-----------------------------------------------------+
| THE NAMESAKE |
| NORSE MYTHOLOGY |
| AMERICAN GODS |
| INTERPRETER OF MALADIES |
| A HOLOGRAM FOR THE KING: A NOVEL |
| THE CIRCLE |
| THE AMAZING ADVENTURES OF KAVALIER & CLAY |
| JUST KIDS |
| A HEARTBREAKING WORK OF STAGGERING GENIUS |
| CORALINE |
| WHAT WE TALK ABOUT WHEN WE TALK ABOUT LOVE: STORIES |
| WHERE I'M CALLING FROM: SELECTED STORIES |
| WHITE NOISE |
| CANNERY ROW |
| OBLIVION: STORIES |
| CONSIDER THE LOBSTER |
+-----------------------------------------------------+대문자로 title들이 나온 것을 볼 수 있다.
INSERT, LEFT, RIGHT, REPEAT, TRIM
INSERT는 더 큰 문자열에 하위 문자열을 삽입할 수 있다. 단, 간단히 삽입이 아니라 변경이라는 점을 알도록 하자.
SELECT INSERT(source, pos, len, insert_str);source의 pos부터 len까지에 insert_str을 대신 넣어준다. 만약 len이 0이라면 그대로 insert_str 값을 추가하는 것이고, len이 양수라면 해당 len만큼 덮어쓴다.
가령 다음의 예제를 보도록 하자.
SELECT INSERT('Quadratic', 3, 4, 'What');
+-----------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+-----------------------------------+
| QuWhattic |
+-----------------------------------+len을 0으로하면 오버라이드하지 않고, 데이터를 추가할 수 있다고 했다. 'Hello Bobby'에서 공백 부분인에 There을 넣어보고 싶다고 하자. 다음과 같이 할 수 있다.
SELECT INSERT('Hello Bobby', 6, 0, 'There');
+--------------------------------------+
| INSERT('Hello Bobby', 6, 0, 'There') |
+--------------------------------------+
| HelloThere Bobby |
+--------------------------------------+여전히 공백도 남아있는 것을 알 수 있고, 그 옆에 There 값이 추가된 것을 볼 수 있다.
LEFT, RIGHT는 왼쪽과 오른쪽 기준으로 일정 글자 수만큼의 문자열을 가져올 수 있다.
SELECT LEFT('omghahalol!', 3);
+------------------------+
| LEFT('omghahalol!', 3) |
+------------------------+
| omg |
+------------------------+omghahalol!에서 맨 왼쪽 3글자를 가져오는 것이다.
SELECT RIGHT('omghahalol!', 3);
+-------------------------+
| RIGHT('omghahalol!', 3) |
+-------------------------+
| ol! |
+-------------------------+omghahalol!에서 맨 오른쪽 3글자를 가져오는 것이다.
SELECT LEFT(author_lname, 1) FROM books;
+-----------------------+
| LEFT(author_lname, 1) |
+-----------------------+
| L |
| G |
| G |
| L |
| E |
| E |
| C |
| S |
| E |
| G |
| C |
| C |
| D |
| S |
| F |
| F |
+-----------------------+REPEAT는 문자열을 일정 횟수만큼 반복하는 것이다.
SELECT REPEAT('ha', 4);
+-----------------+
| REPEAT('ha', 4) |
+-----------------+
| hahahaha |
+-----------------+TRIM은 앞 뒤의 공백을 제거하는 것이다.
SELECT TRIM(' san antonio ');
+-------------------------+
| TRIM(' san antonio ') |
+-------------------------+
| san antonio |
+-------------------------+TRIM은 LEADING, BOTH, TRAILING을 사용하여 특정 문자들을 삭제할 수 있는데, 다음과 같이 사용할 수 있다.
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxxxx');
+--------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxxxx') |
+--------------------------------------+
| barxxxxx |
+--------------------------------------+
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxxxx');
+-----------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxxxx') |
+-----------------------------------+
| bar |
+-----------------------------------+
SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxxxx');
+---------------------------------------+
| TRIM(TRAILING 'x' FROM 'xxxbarxxxxx') |
+---------------------------------------+
| xxxbar |
+---------------------------------------+좀 복잡해보이긴 해도 꽤나 유용한 sql문이다.
R3의 망령