/* 032- String Functions */
-- create table "students" and insert records --
CREATE TABLE students(
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 students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- String Functions --
SELECT id, UPPER(name) AS Name , percentage
FROM students;
SELECT id, UCASE(name) AS Name , percentage
FROM students;
SELECT id, LOWER(name) AS Name , percentage
FROM students;
SELECT id, LCASE(name) AS Name , percentage
FROM students;
SELECT id, name,CHARACTER_LENGTH(name) AS Characters
FROM students;
SELECT id, name,CHAR_LENGTH(name) AS Characters
FROM students;
SELECT id, name,LENGTH(name) AS Characters
FROM students;
SELECT id, CONCAT(name, " " ,percentage) AS Name
FROM students;
SELECT id, CONCAT(name, " - " ,percentage) AS Name
FROM students;
SELECT CONCAT("Yahoo","Baba","Youtube","Channel") AS Name;
SELECT CONCAT_WS("Yahoo","Baba","Youtube","Channel") AS Name;
SELECT CONCAT_WS(" - ","Baba","Youtube","Channel") AS Name;
SELECT " Yahoo Baba " AS Name;
SELECT LTRIM(" Yahoo Baba ") AS Name;
SELECT " Yahoo Baba " AS Name;
SELECT RTRIM(" Yahoo Baba ") AS Name;
SELECT TRIM(" Yahoo Baba ") AS Name;
SELECT POSITION("Baba" IN "Yahoo Baba") AS Name;
SELECT POSITION("Baba" IN "Yahoo Baba Baba") AS Name;
SELECT POSITION("a" IN "Yahoo Baba Baba") AS Name;
SELECT INSTR("Yahoo Baba Baba","a") AS Name;
SELECT INSTR("Yahoo Baba Baba","Baba") AS Name;
SELECT INSTR("Yahoo Baba Baba","hoo") AS Name;
SELECT LOCATE("hoo","Yahoo Baba Baba") AS Name;
SELECT LOCATE("a","Yahoo Baba Baba") AS Name;
SELECT LOCATE("a","Yahoo Baba Baba",3) AS Name;