MySQL String Functions - II

 How to work List of String Function-||:

Code:

CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

    percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

    courses INT NOT NULL,

    PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid),

    FOREIGN KEY (courses) REFERENCES Courses (course_id)  

);

INSERT INTO personal(id,name,percentage,age,gender,city,courses)

VALUES

(1,"Aqib","45","13","M",1,1),

(2,"Khadija","56","21","F",2,2),

(3,"Salman Khan","62","20","M",1,1),

(4,"Aqeela","47","18","F",3,1),

(5,"Saqib","74","22","M",1,3),

(6,"Abraham","64","21","M",2,2),

(7,"Shahid","52","20","M",1,3);

Examples:

SELECT SUBSTRING("Yahoo Baba",3);

SELECT SUBSTRING("Yahoo Baba",3) AS Name;

SELECT SUBSTRING("Yahoo Baba",5) AS Name;

SELECT SUBSTRING("Yahoo Baba",3,6) AS Name;

SELECT SUBSTRING("Yahoo Baba",-6,3) AS Name;

SELECT SUBSTR("Yahoo Baba",-6,3) AS Name;

SELECT MID("Yahoo Baba",-6,3) AS Name;

SELECT SUBSTRING_INDEX("www.yahoobaba.net",".",1) AS Name;

SELECT SUBSTRING_INDEX("www.yahoobaba.net",".",2) AS Name;

SELECT SUBSTRING_INDEX("www.yahoobaba.net","o",2) AS Name;

SELECT LEFT("Yahoo Baba",3) AS Name;

SELECT LEFT("Yahoo Baba",5) AS Name;

SELECT RIGHT("Yahoo Baba",5) AS Name;

SELECT RIGHT("Yahoo Baba",3) AS Name;

SELECT RPAD("Yahoo Baba",20,"-") AS Name;

SELECT RPAD("Yahoo Baba",20,"ABC") AS Name;

SELECT LPAD("Yahoo Baba",20,"*") AS Name;

SELECT SPACE(100) AS Name;

SELECT REVERSE("Yahoo Baba") AS Name;

SELECT REPLACE("Yahoo Baba","Baba","Wow") AS Name;

SELECT REPLACE("Yahoo Baba Yoo Baba","Baba","Wow") AS Name;

SELECT STRCMP("Yahoo Baba","yahoo baba") AS Name;

SELECT STRCMP("Yahoo Baba","yahoo ") AS Name;

SELECT STRCMP("Yahoo ","yahoo baba") AS Name;

SELECT FIELD("a","X","a","k") AS Name;

SELECT FIELD("a","X","A","k") AS Name;

SELECT FIELD(5,0,1,2,3,4,5) AS Name;

SELECT FIELD("ram","Ram","Mohan","Shyam") AS Name;

SELECT FIND_IN_SET("ram","Ram,Mohan,Shyam") AS Name;

SELECT FIND_IN_SET("Mohan","Ram, Mohan, Shyam") AS Name;

SELECT FIND_IN_SET("Mohan","Ram,Mohan,Shyam") AS Name;

SELECT FORMAT(255.3568,2) AS Value;

SELECT FORMAT(255.3568,3) AS Value;

SELECT HEX("Yahoo Baba") AS Value;