CODEWITHSUNDEEP
mysqlsqlsubstringconcatenation
cvoep28
cvoep28

Reputation: 423

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?

Upvotes: 1

Views: 37040

Answers (4)

rbnhd
rbnhd

Reputation: 11

In oracle, you can use something like

select concat(substr(fname,1,3), substr(lname,1,3)) as username from <table_name>;

Note that u can use substr to trim integer datatypes too, while left() might only work for varchar.

Upvotes: 0

ABC
ABC

Reputation: 11

Try the below query

select first_name,
       substr(first_name,1,2)||substr(lower(last_name),1,5) as "USER NAME",
       last_name 
from employees;

Upvotes: -1

saara
saara

Reputation: 11

Use the below lines

select  concat(left(empfname, 2) + ' ',left(emplname, 2)) UserNamefrom employee

Upvotes: -1

Taryn
Taryn

Reputation: 247690

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 |

Upvotes: 5

Related Questions