Syntax error Extract the user ID from the username only in MySQL?

Extract the user ID from the username only in MySQL?

To extract the User ID only from MySQL, you can use SUBSTRING_INDEX(), which extracts the part of a string from the Username to get the User ID. 

Let us first display the user −

mysql> SELECT USER();

This will produce the following output −

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Let us now extract the UserID only −

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

This will produce the following output −

+-------------------------------+
| SUBSTRING_INDEX(USER(),'@',1) |
+-------------------------------+
| root                          |
+-------------------------------+
1 row in set (0.00 sec)

Additionally, if you want the host name, you can use the below query −

mysql> SELECT SUBSTRING_INDEX(USER(),'@',-1);

This will produce the following output −

+--------------------------------+
| SUBSTRING_INDEX(USER(),'@',-1) |
+--------------------------------+
| localhost                      |
+--------------------------------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements