MySQL_section7_The world of string functions

#1. SOURCE : MySQL에서 텍스트 파일의 SQL문 실행. 외부 파일의 SQL문을 실행
::SOURCE 경로/파일명.sql
!! 주의 : SOURCE 명령어의 파일명을 쓸때는 반드시 '\' 대신에 '/'를 써주어야 함
EX1. SOURCE d:/mysql/test.sql;

 

#2. CONCAT : column 병합
:: SELECT CONCAT(column, another column) FROM (테이블명);
:: SELECT CONCAT(string값, string값);
:: SELECT CONCAT(column, '문자 사이에 넣고 싶은 문자', another column) FROM (테이블명);
:: SELECT CONCAT(string값, '문자 사이에 넣고 싶은 문자', string값);
:: SELECT CONCAT(column_1, '-', column_2, '-', column_3) FROM (테이블명);
:: SELECT CONCAT_WS('-', column_1, column_2, column_3) FROM (테이블명); #Concat with Saparator

EX1. SELECT CONCAT('Hello', 'World');
EX2. SELECT CONCAT('Hello', '...', 'World');
EX3. SELECT CONCAT(author_fname, ' ', author_lname) FORM books;
EX4. SELECT CONCAT(author_fname, ' ', author_lname) AS 'full name' FORM books;
EX5. SELECT author_fname AS first, author_lname AS last, CONCAT(author_fname, ' ', author_lname) AS full name FORM books;
EX6. SELECT CONCAT(title, '-', author_fname, '-', author_lname) FROM books;
EX7. SELECT CONCAT_WS('-', title, author_fname, author_lname) FROM books;

 

#3. SUBSTRING : 문자 슬라이싱
::SELECT SUBSTRING(문자열, slicing 범위);
::SELECT SUBSTRING(컬럼명, slicing 범위);
!! SUBSTRING = SUBSTR

EX1. SELECT SUBSTRNIG ('Hello world', 1, 4); => 출력 : Hell
EX2. SELECT SUBSTRING('Hello World', 7); => 출력 : World
EX3. SELECT SUBSTRING('Hello World', -3); => 출력 : rld
EX4. SELECT SUBSTRING('Hello World', -7); => 출력 : o World
EX5. SELECT SUBSTRING("Where I'm calling From: Selected Stories", 1, 10); => 출력 : Where I'm
     SELECT SUBSTR("Where I'm calling From: Selected Stories", 1, 10);
EX6. SELECT SUBSTRING(title, 1, 10) FROM books;
     SELECT SUBSTR(title, 1, 10) FROM books;
EX7. "Want to show dot"
     SELECT CONCAT (SBUSTR(title, 1, 10), '...') FROM books;

 

#4. REPLACE : 문자 임의로 변경
::SELECT REPLACE(문자열 혹은 컬럼명, 바꿀문자, 대체문자)
EX1. SELECT REPLACE('Hello World', 'Hell', '****'); => 출력 : ****o World
EX2. SELECT REPLACE('Hello World', 'o', 0); => 출력 : Hell0 W0rld
EX3. SELECT SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string' FROM books;
EX4. SELECT CONCAT(SUBSTR(title, 1, 10), '...') AS 'short title' FROM books;

 

#5. REVERSE : 문자열 거꾸로 변경
::SELECT REVERSE(문자열);
::SELECT REVERSE(컬럼명) FROM <테이블명>;
EX1. SELECT REVERSE('meow meow');
EX2. SELECT REVERSE(author_fname) FROM books;
EX3. SELECT CONCAT('woof', REVERSE('woof'));
EX4. SELECT CONCAT(author_name, ' ', REVERSE(author_fname)) FROM books;

 

#6. CHAR_LENGTH : 문자열의 길이 카운트
::SELECT CHAR_LENGTH(문자열);
::SELECT CHAR_LENGTH(컬럼명) FROM <테이블명>;
EX1. SELECT CHAR_LENGTH('Hello world');
EX2. SELECT author_lname, CHAR_LENGTH(author_lname) FROM books;
EX3. SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long');

 

#7. UPPER & LOWER : 대소문자 바꾸기
::SELECT UPPER(컬럼명) FROM <테이블명>;
::SELECT LOWER(컬럼명) FROM <테이블명>;
EX1. SELECT UPPER('Hello world');
EX2. SELECT LOWER('Hello world');
EX3. SELECT UPPER(title) FROM books;
EX4. SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
EX5. SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;

 

#8. EXERCISE

 

 (1) Reverse and uppercase the following sentence "Why does my cat look at me with such hatred?"
  SELECT REVERSE(UPPER("Why does my cat look at me with such hatred?"));

 

 (2) SELECT REPLACE(CONCAT('I', ' ', 'like', ' ', 'cats'), ' ', '-');

 

 (3) SELECT title, REPLACE(CONCAT(title), ' ', '->') FROM books;

 

 (4) SELECT author_lname AS forwards, REVERSE(author_lname) AS backwards FROM books;

 

 (5) SELECT

  CONCAT(UPPER(author_fname), ' ', UPPER(author_lname)) AS 'full name in_caps'
     FROM books;

 

 (6) SELECT
  CONCAT(title, ' was released in ', released_year) AS 'blurb'
     FROM books;

 

 (7) SELECT
  title,
  CHAR_LENGTH(title) AS 'character count'
     FROM books;

 

 (8) SELECT
  CONCAT(SUBSTR(title, 1, 10), '...') AS 'short title',
  CONCAT(SUBSRT(author_lname, 1, 6), ',' SUBSTR(author_fname, 1, 4)) AS 'author',
  CONCAT(stock_quantity, ' in stock') AS 'quantity'
     FROM books;