SUBSTRING()

Description

SUBSTR() is a synonym of SUBSTRING(). The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos.

Syntax

  1. > SUBSTRING(str,pos)
  2. > SUBSTR(str,pos,len)

Arguments

ArgumentsDescription
strRequired. CHAR and VARCHAR are both supported
posRequired.
lenOptional.

Examples

  1. > CREATE TABLE IF NOT EXISTS t1 (
  2. pub_id varchar(8) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  3. pub_name varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  4. pub_city varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  5. country varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  6. country_office varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  7. no_of_branch int NOT NULL DEFAULT 0,
  8. estd date NOT NULL DEFAULT '2000-01-01'
  9. );
  11. > INSERT INTO t3 (pub_id, pub_name, pub_city, country, country_office, no_of_branch, estd) VALUES
  12. ('P001', 'Jex Max Publication', 'New York', 'USA', 'New York', 15, '1969-12-25'),
  13. ('P002', 'BPP Publication', 'Mumbai', 'India', 'New Delhi', 10, '1985-10-01'),
  14. ('P003', 'New Harrold Publication', 'Adelaide', 'Australia', 'Sydney', 6, '1975-09-05'),
  15. ('P004', 'Ultra Press Inc.', 'London', 'UK', 'London', 8, '1948-07-10'),
  16. ('P005', 'Mountain Publication', 'Houstan', 'USA', 'Sun Diego', 25, '1975-01-01'),
  17. ('P006', 'Summer Night Publication', 'New York', 'USA', 'Atlanta', 10, '1990-12-10'),
  18. ('P007', 'Pieterson Grp. of Publishers', 'Cambridge', 'UK', 'London', 6, '1950-07-15'),
  19. ('P008', 'Novel Publisher Ltd.', 'New Delhi', 'India', 'Bangalore', 10, '2000-01-01');
  21. > SELECT pub_name, SUBSTR(pub_name,4,5) FROM t1 WHERE country='USA';
  22. +--------------------------+------------------------+
  23. | pub_name                 | substr(pub_name, 4, 5) |
  24. +--------------------------+------------------------+
  25. | Jex Max Publication      |  Max                   |
  26. | Mountain Publication     | ntain                  |
  27. | Summer Night Publication | mer N                  |
  28. +--------------------------+------------------------+
  29. 3 rows in set (0.04 sec)
  31. > SELECT pub_name, SUBSTR(pub_name,5) FROM t1 WHERE country='USA';
  32. +--------------------------+----------------------+
  33. | pub_name                 | substr(pub_name, 5)  |
  34. +--------------------------+----------------------+
  35. | Jex Max Publication      | Max Publication      |
  36. | Mountain Publication     | tain Publication     |
  37. | Summer Night Publication | er Night Publication |
  38. +--------------------------+----------------------+
  39. 3 rows in set (0.03 sec)

Constraints

  • Currently, Substring() doesn’t support FROM and FOR clauses.