Search code examples
mysql

split characters and numbers in MySQL

I have a column in my table like this,

students
--------
abc23
def1
xyz567
......

and so on. Now i need output like only names Need output as

students
--------
abc
def
xyz

How can i get this in mysql. Thanks advance.

Solution

  • You can do it with string functions ans some CAST() magic:

    SELECT 
  SUBSTR(
    name, 
    1, 
    CHAR_LENGTH(@name) - CHAR_LENGTH(
      IF(
        @c:=CAST(REVERSE(name) AS UNSIGNED),
        @c,
        ''
      )
    )
  ) 
FROM 
  students

    for example:

    SET @name:='abc12345';

mysql> SELECT SUBSTR(@name, 1, CHAR_LENGTH(@name) - CHAR_LENGTH(IF(@c:=CAST(REVERSE(@name) AS UNSIGNED), @c, ''))) AS name;
+------+
| name |
+------+
| abc  |
+------+