How to use SQL CONCAT/SUBSTR?

Question

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 1

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

Solution 2

You seem to be confusing the concat function, substr function and the concatentation operator (||). You aren't using substr to get the first character of the first name, and to restirct the length of both you need to provide both the starting position and the substring length.

You're also referring to m.firstname without defining m as an alias for your table name; an alias isn't really needed here as there is only one table but if you use it, use it consistently.

To get the first letter of the firstname and the first five letters of the surname you could use:

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

or

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

If you're updating a column in the same table though, rather than using a PL/SQL into clause, you need an update not a select:

UPDATE marketing
SET username = SUBSTR(firstname, 1, 1) || SUBSTR(surname, 1, 5)
WHERE marketing_id = 10001;

SQL Fiddle demo.

Solution 3

The m in m.name needs to have been defined, that's missing. CONCAT (MySQL?) should consume strings, you pass a number (1) in addition to a string. Your description mentions two substrings, the statement has just one, measured by occurrences of SUBSTR. The concatenation operator, if supported by your dialect of SQL, will then not see what you intended it to see (two SUBSTRings).