Use SUBSTRING_INDEX syntax on MySQL database version 5.5.62

[UncleVince]

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');

[m_hutley]

@UncleVince

Without success because the first row is wrong

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).

[UncleVince]

@m_hutley

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).

Awesone, thank you so much for this help!