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?
select concat(substring(fname, 1, 2), substring(lname, 1, 5)) UserName from yourtable
See SQL Fiddle with Demo.
Or you can use
select concat(left(fname, 2), left(lname, 5)) UserName from yourtable
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 |