Skip to main content

MySQL practical Tutorials part 3- String Functions CONCAT, SUBSTRING, REVERSE, CHAR_LENGTH, REPLACE

 CODE: Loading Our Book Data

1. First create TABLE with the following code:


DROP DATABASE IF EXISTS book_shop;

CREATE DATABASE book_shop;

USE book_shop; 

 

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);

2. Then source that file

source book_data.sql 


3. Now check your work:


DESC books;

SELECT * FROM books; 

=======================================================================================================================

CODE: Working With CONCAT

SELECT author_fname, author_lname FROM books;

 

CONCAT(x,y,z) // from slides

 

CONCAT(column, anotherColumn) // from slides

 

CONCAT(author_fname, author_lname)

 

CONCAT(column, 'text', anotherColumn, 'more text')

 

CONCAT(author_fname, ' ', author_lname)

 

CONCAT(author_fname, author_lname); // invalid syntax

 

SELECT CONCAT('Hello', 'World');

 

SELECT CONCAT('Hello', '...', 'World');

 

SELECT

  CONCAT(author_fname, ' ', author_lname)

FROM books;

 

SELECT

  CONCAT(author_fname, ' ', author_lname)

  AS 'full name'

FROM books;

 

SELECT author_fname AS first, author_lname AS last, 

  CONCAT(author_fname, ' ', author_lname) AS full

FROM books;

 

SELECT author_fname AS first, author_lname AS last, 

  CONCAT(author_fname, ', ', author_lname) AS full

FROM books;

 

SELECT CONCAT(title, '-', author_fname, '-', author_lname) FROM books;

 

SELECT 

    CONCAT_WS(' - ', title, author_fname, author_lname) 

FROM books;


99. Working with CONCAT

101. Introducing SUBSTRING

=======================================================================================================================


CODE: Introducing SUBSTRING

SELECT SUBSTRING('Hello World', 1, 4);

 

SELECT SUBSTRING('Hello World', 7);

 

SELECT SUBSTRING('Hello World', 3, 8);

 

SELECT SUBSTRING('Hello World', 3);

 

SELECT SUBSTRING('Hello World', -3);

 

SELECT SUBSTRING('Hello World', -7);

 

SELECT title FROM books;

 

SELECT SUBSTRING("Where I'm Calling From: Selected Stories", 1, 10);

 

SELECT SUBSTRING(title, 1, 10) FROM books;

 

SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;

 

SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;

 

SELECT CONCAT

    (

        SUBSTRING(title, 1, 10),

        '...'

    )

FROM books;

 

SELECT CONCAT

    (

        SUBSTRING(title, 1, 10),

        '...'

    ) AS 'short title'

FROM books;

 

 Introducing SUBSTRING

 Introducing REPLACE



=======================================================================================================================

CODE: Introducing REPLACE

SELECT REPLACE('Hello World', 'Hell', '%$#@');

 

SELECT REPLACE('Hello World', 'l', '7');

 

SELECT REPLACE('Hello World', 'o', '0');

 

SELECT REPLACE('HellO World', 'o', '*');

 

SELECT

  REPLACE('cheese bread coffee milk', ' ', ' and ');

 

SELECT REPLACE(title, 'e ', '3') FROM books;

 

-- SELECT

--    CONCAT

--    (

--        SUBSTRING(title, 1, 10),

--        '...'

--    ) AS 'short title'

-- FROM books;

 

SELECT

    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10)

FROM books;

 

SELECT

    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string'

FROM books;

- The REPLACE() function, as well as the other string functions, only change the query output, they don't affect the actual data in the database.


=======================================================================================================================

CODE: Using REVERSE

SELECT REVERSE('Hello World');

 

SELECT REVERSE('meow meow');

 

SELECT REVERSE(author_fname) FROM books;

 

SELECT CONCAT('woof', REVERSE('woof'));

 

SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

=======================================================================================================================

CODE: Working with CHAR LENGTH

SELECT CHAR_LENGTH('Hello World');

 

SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;

 

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

=======================================================================================================================

CODE: Changing Case with UPPER and LOWER

SELECT UPPER('Hello World');

 

SELECT LOWER('Hello World');

 

SELECT UPPER(title) FROM books;

 

SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;

 

SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;


Note about string functions

I have two important notes for you.


Firstly, before you move onto the next, please remember that order is important when dealing with combining/wrapping certain string functions.


For example:


This works:


SELECT UPPER(CONCAT(author_fname, ' ', author_lname)) AS "full name in caps"

FROM books;

While this does not:


SELECT CONCAT(UPPER(author_fname, ' ', author_lname)) AS "full name in caps" 

FROM books;

UPPER only takes one argument and CONCAT takes two or more arguments, so they can't be switched in that way.


You could do it this way, however:


SELECT CONCAT(UPPER(author_fname), ' ', UPPER(author_lname)) AS "full name in caps" 

FROM books;

=======================================================================================================================

 CODE: String Function Challenges Solution

SELECT REVERSE(UPPER('Why does my cat look at me with such hatred?')); 


SELECT UPPER(REVERSE('Why does my cat look at me with such hatred?')); 


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


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


SELECT 

   author_lname AS forwards,

   REVERSE(author_lname) AS backwards 

FROM books;



SELECT

   UPPER

   (

      CONCAT(author_fname, ' ', author_lname)

   ) AS 'full name in caps'

FROM books;



SELECT

   CONCAT(title, ' was released in ', released_year) AS blurb

FROM books;

SELECT

   title,

   CHAR_LENGTH(title) AS 'character count'

FROM books;



SELECT

   CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title',

   CONCAT(author_lname, ',', author_fname) AS author,

   CONCAT(stock_quantity, ' in stock') AS quantity

FROM books;



CODE: Seed Data: Adding A Couple New Books

INSERT INTO books

    (title, author_fname, author_lname, released_year, stock_quantity, pages)

    VALUES ('10% Happier', 'Dan', 'Harris', 2014, 29, 256), 

           ('fake_book', 'Freida', 'Harris', 2001, 287, 428),

           ('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);

 

 

SELECT title FROM books;


Labels:
Location: Ahmednagar, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Add, remove, search an item in listview in C#

Below is the C# code which will help you to add, remove and search operations on listview control in C#. Below is the design view of the project: Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace Treeview_control_demo {     public partial class Form2 : Form     {         public Form2()         {             InitializeComponent();             listView1.View = View.Details;                   }         private void button1_Click(object sender, EventArgs e)         {             if (textBox1.Text.Trim().Length == 0)...
Post a Comment
Read more

display files and directories in Listview

Below is the C# code which displays all files and directories in listview control with their file size and creation date. If it is file then it also displays the extension of the file e.g. .txt, .jpg etc Below is the design view of the project: Listview to display files and directories with size and date created Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.IO; namespace search_in_listview {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();                   }         private void button1_Click(object sender, EventArgs ...
Post a Comment
Read more

Add worklog in Jira using Python

 Below is the Python code to add the worklog in Jira. You need to install a request library for this. Here is the code: import requests from requests.auth import HTTPBasicAuth import json url = "https://your jira address here/rest/api/2/issue/ticket_number/worklog" auth = HTTPBasicAuth("username", "jira access token") headers = {     "Accept": "application/json",     "Content-Type": "application/json" } payload = json.dumps({     "comment": {         "content": [             {                 "content": [                     {                         "text": "This is for QA Testing",                         "type": "text"                     } ...
Post a Comment
Read more