I want to remove string after space ' ', but length is variable and string can be of any length. for Example ;-

 as full name Nikhil Sharma
              Nikhil Arora
              Digvender Chauhan

i want to remove all words after space

UPDATE MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

This is not working Answer is with help of CAse, Regexp.

Help would be highly appreciated.

CodePudding user response：

In MySQL the function name is LOCATE(). Then search for ' ' instead of ';'.

UPDATE MyTable
SET MyText = LEFT(MyText, LOCATE(' ', MyText)-1)
WHERE LOCATE(' ', MyText) != 0

DEMO

Explanation:

LOCATE(' ', MyText) returns the position of the first space in MyText.

LEFT(MyText, n) returns the leftmost n characters of MyText.

So if the space is in position 4, we subtract 1 from that position, and return the leftmost 3 characters. That removes everything from the space to the end of the string.

LOCATE() returns 0 if it can't find the substring, so the test WHERE LOCATE(' ', MyText) != 0 makes it only update rows where the column contains a space.

CodePudding user response：

SELECT SUBSTRING_INDEX(column_name, ' ', 1) FROM table_name;

and

UPDATE table_name SET column_name = SUBSTRING_INDEX(column_name, ' ', 1);

accordingly.