Use SUBSTRING_INDEX syntax on MySQL database version 5.5.62

Hi all,

The version of MySQL database is 5.5.62

This is my table

mysql> SELECT * FROM `dotablenew`;
+-----+-------------+
| sID | sStringNew  |
+-----+-------------+
|   1 | D1080 ARE A |
|   2 | D5258 SEG   |
|   3 | D0749 COR   |
|   4 | D4278 VAI   |
|   5 | D2664 PON   |
+-----+-------------+
5 rows in set

I need this return

+-------+------+
| sOne  | sTwo |
+-------+------+
| D1080 | ARE  |
| D5258 | SEG  |
| D0749 | COR  |
| D4278 | VAI  |
| D2664 | PON  |
+-------+------+

And I have tried this solution

mysql> SELECT
	SUBSTRING_INDEX(sStringNew, ' ', 1) AS sOne,
	SUBSTRING_INDEX(sStringNew, ' ', - 1) AS sTwo
FROM
	`dotablenew`;
+-------+------+
| sOne  | sTwo |
+-------+------+
| D1080 | A    |
| D5258 | SEG  |
| D0749 | COR  |
| D4278 | VAI  |
| D2664 | PON  |
+-------+------+
5 rows in set

Without success because the first row is wrong

+-------+------+
| sOne  | sTwo |
+-------+------+
| D1080 | A    |
+-------+------+

I really don’t know how many spaces can be in the string sStringNew but I always need to extract the first two elements of the string sStringNew separated by space…

How to do resolve this?

Please, any suggestion

My table below

DROP TABLE IF EXISTS `dotablenew`;
CREATE TABLE `dotablenew` (
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  `sStringNew` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dotablenew
-- ----------------------------
INSERT INTO `dotablenew` VALUES ('1', 'D1080 ARE A');
INSERT INTO `dotablenew` VALUES ('2', 'D5258 SEG');
INSERT INTO `dotablenew` VALUES ('3', 'D0749 COR');
INSERT INTO `dotablenew` VALUES ('4', 'D4278 VAI');
INSERT INTO `dotablenew` VALUES ('5', 'D2664 PON');

No it’s not.

You told it: “Take sStringNew, find the last instance of a space, and return everything to the right of it.”

It dutifully did what you told it to do, and returned A.

There are a few ways to do what you want, but sticking as close to your original attempt, I would do this:

SUBSTRING_INDEX(SUBSTRING_INDEX(sStringNew, ' ',2),' ', - 1) AS sTwo

So what that will do, is take sStringNew, take the first two ‘tokens’ (so D1080 ARE), and then take the last token from that string (resulting in ARE).

2 Likes

Awesone, thank you so much for this help!

1 Like