MYSQL, How to combine the first 2 letters of the first name and the first 5 letters of the last name

I’m trying to write an SQL query (using the SELECT function) that will take the first two letters of the first name and the first 5 letters of the last name, then combine them and put them in a new column called “User Name.” I’ve tried using the SUBSTRING and CONCAT functions together, but my syntax isn’t right. Does anyone know how this could be done?

Answer

You can use both CONCAT() and SUBSTRING():

select 
  concat(substring(fname, 1, 2), substring(lname, 1, 5)) UserName
from yourtable

See SQL Fiddle with Demo.

Or you can use LEFT() with CONCAT():

select 
  concat(left(fname, 2), left(lname, 5)) UserName
from yourtable

See SQL Fiddle with Demo

If your sample data is:

CREATE TABLE yourtable (`fname` varchar(4), `lname` varchar(50)) ;

INSERT INTO yourtable (`fname`, `lname`)
VALUES ('John', 'Smithton'),
    ('Abby', 'Jonesing');

Both queries will return:

| USERNAME |
------------
|  JoSmith |
|  AbJones |