CODEWITHSUNDEEP
sqloracle-database
CuzWhyNot Vlogs
CuzWhyNot Vlogs

Reputation: 31

Concatenating the first character of first name and the last name in Oracle SQL

Question: New to Oracle SQL and cant figure this out. Any Help is appreciated. :)

In table employee2, generate the email address for column username for each student by concatenating the first character of employee’s first name and the employee’s last name. For instance, the username of employee Peter Stone will be pstone. NOTE: the username is in all lower case letters.

What I have Tried:

select 
  concat(left(firstname, 1) + ' ',left(lastname)) UserName
from employee2;

select concat(substring(firstname, 1),substring(lastname)) UserName from employee2;

Upvotes: 1

Views: 6289

Answers (6)

user9950041
user9950041

Reputation: 

select lower(concat(substr(firstname,1,1),lastname)) Username from employee2;

or 

select lower(substr(firstname,1,1)||lastname)  from employee2;

Insert with select

SQL> create table test (id number,uname varchar2(100));

SQL> insert into test
  2  select rownum,lower(concat(substr(first_name,1,1),last_name))||'@gmail.com'
  3  from employees;

108 rows created.


SQL> commit;

Commit complete.


SQL> select * from test
  2  fetch first 5 rows only;

        ID UNAME
---------- ----------------------------------------------------------------------------------------------------
         1 [email protected]
         2 [email protected]
         3 [email protected]
         4 [email protected]
         5 [email protected]


SQL>

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You should use substr and concatanation operator || as follows:

Update employee2
   Set username = lower(substr(firstname,1,1) || lastname)

Benifit of using || over concat function is that concat need to be called multiple times if there are more than two strings need to be combined. || is string operator and easy to use and read.

Upvotes: 2

CuzWhyNot Vlogs
CuzWhyNot Vlogs

Reputation: 31

UPDATE employee2
SET username = lower(CONCAT(substr(firstname, 1,1),lastname));

Thanks everyone for your help, might have not explained my question correctly but this is the output i wanted to achieve.

Upvotes: 0

Strath_SP
Strath_SP

Reputation: 24

MERGE INTO employee 2 
USING (select (substr(firstname, 1,1) || lower(lastname)) as UserName 
 from employee 1) ilv;
ON ilv.username = 2.username
   
    WHEN NOT MATCHED THEN
        INSERT (username)
        values(ilv.username);

Upvotes: 0

Mureinik
Mureinik

Reputation: 311073

There are a few issues here:

  1. Oracle doesn't have a left function - but you could use substr to generate that effect.
  2. Strings in Oracle are concatenated by the || operator, not +
  3. You should convert the username to lowercase:
SELECT TOLOWER(SUBSTR(firstname, 1, 1) || lastname) AS username
FROM   employee2

Upvotes: 0

Strath_SP
Strath_SP

Reputation: 24

select (substr(firstname, 1,1) || lower(lastname)) as UserName 
 from employee2;

Upvotes: 0

Related Questions