Search code examples
sqloracle-databaseconcatenationsubstr

How to use SQL CONCAT/SUBSTR?

I am trying to create a username for each person in the marketing table. I want an update statement that inserts the new username into the username column.

I want to take the first letter of the firstname, join to the first 5 letters of the surname, and save this into the username column of the marketing table. I need to put this inside a procedure somehow too.

heres my code:

SELECT CONCAT(m.firstname, 1)||SUBSTR(m.surname, 5)
INTO username
FROM marketing
WHERE marketing_id = 10001;

However, I am getting errors that I cannot seem to fix, like firstname and surname are invalid identifiers. Any help here is appreciated.

Solution 
  • update username
set username= (select concat(left(firstname,1),left(surname,5))
                  from marketing
                  WHERE marketing_id = 10001)
WHERE marketing_id = 10001;