MySQL - 문자열 컬럼의 데이터를 가공하는 여러함수들 예제풀이

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),
('10% Happier', 'Dan', 'Harris', 2014, 29, 256),
('fake_book', 'Freida', 'Harris', 2001, 287, 428),
('Lincoln In the Bardo', 'George', 'Saunders', 2017, 156, 375);

위 데이터들을 가지고 예제풀이를 해보겠습니다.

 

1. 타이틀의 공백을 '->'로 바꿔서 나오도록 조회하시오.

select replace(title,' ','->') 
from books;

2. 다음 컬럼처럼 나오게 조회하시오.

select author_lname as forwards , 
reverse(author_lname) as backwards 
from books;

3. 다음처럼 이름을 합치되, 대문자로 합쳐서 조회하시오.

select upper(concat(author_fname,' ',author_lname))
as fullnameincaps
from books;

4. 다음처럼 타이틀 컬럼과 년도 컬럼을 합치되, 'was released in'이 들어가도록

합쳐서 조회하시오.

select concat(title,'was released in',released_year) as blurb
from books;

5. 다음처럼 타이틀과, 타이틀에 적힌 글자의 갯수가 나오도록 조회하시오.

select title , char_length(title) as charactercount
from books;

6. 다음처럼 조회하시오

단, 숏타이틀은 앞에서 10글자까지만 나오고, 뒤에 '...'이 나오도록 만들고, author는 이름 두개 컬럼을 합치고(f_name,l_name), quantity는 원래 숫자에 in stock이 붙도록 조회하시오.

select concat(substring(title,1,10), '...' ) as shorttitle ,
 concat(author_lname, ',' ,author_fname) as author , 
 concat( stock_quantity,' in stock') as quantity
from books;