SUBSTRING()

Function Description

SUBSTR() and MID() are both synonyms for SUBSTRING(). Writing without the len parameter returns a substring starting from the pos position. Writing with len parameter returns a substring of length len starting from the pos position.

grammar

> SUBSTRING(str,pos)
> SUBSTR(str,pos,len)

Parameter definition

Parameters	Description
str	Required parameter, alpha string. Both CHAR and VARCHAR types are available.
pos	Required parameters, start position
len	Optional parameter, return the length of the substring

Example

> CREATE TABLE IF NOT EXISTS t1 (
pub_id varchar(8) COLLATE latin1_general_ci NOT NULL DEFAULT '',
pub_name varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
pub_city varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
country varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
country_office varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
no_of_branch int NOT NULL DEFAULT 0,
estd date NOT NULL DEFAULT '2000-01-01'
);

> INSERT INTO t1 (pub_id, pub_name, pub_city, country, country, country_office, no_of_branch, estd) VALUES
('P001', 'Jex Max Publication', 'New York', 'USA', 'New York', 15, '1969-12-25'),
('P002', 'BPP Publication', 'Mumbai', 'India', 'New Delhi', 10, '1985-10-01'),
('P003', 'New Harrold Publication', 'Adelaide', 'Australia', 'Sydney', 6, '1975-09-05'),
('P004', 'Ultra Press Inc.', 'London', 'UK', 'London', 8, '1948-07-10'),
('P005', 'Mountain Publication', 'Houstan', 'USA', 'Sun Diego', 25, '1975-01-01'),
('P006', 'Summer Night Publication', 'New York', 'USA', 'Atlanta', 10, '1990-12-10'),
('P007', 'Pieterson Grp. of Publishers', 'Cambridge', 'UK', 'London', 6, '1950-07-15'),
('P008', 'Novel Publisher Ltd.', 'New Delhi', 'India', 'Bangalore', 10, '2000-01-01');

> SELECT pub_name, SUBSTR(pub_name,4,5) FROM t1 WHERE country='USA';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pub_name | substr(pub_name, 4, 5) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Jex Max Publication | Max |
| Mountain Publication | ntain |
| Summer Night Publication | mer N |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.04 sec)

> SELECT pub_name, SUBSTR(pub_name,5) FROM t1 WHERE country='USA';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pub_name | substr(pub_name, 5) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Jex Max Publication | Max Publication |
| Mountain Publication | tain Publication |
| Summer Night Publication | er Night Publication |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.03 sec)