MySQL query to append character to each entry

Question

I have a table of users which has a username column consisting of a six digit number e.g 675381, I need to append a zero to each of these usernames e.g. 0675381 would be the final output of the previous example, is there a query that could handle this?

Answer 1

UPDATE Tablename SET Username = Concat('0', Username);

Answer 2

what type is the column of?

if it's string type, try something like this:

UPDATE your_table SET column_name=concat('0',column_name);

Answer 3

You mean "prepend" ? i.e. add it on the front?

Is the column numeric? Do you always want 7 characters output?

Assuming that, something like this would work for a query:

select LPAD(CONVERT(username, CHAR), 7, '0')

If the column is characters, the CONVERT() part is unnecessary, just LPAD the username.

If you want to permanently modify the value in the table, you'll need to ensure the column is a character type and UPDATE using the above.

Answer 4

You might want to use CONCAT_WS('', '0', Username) because if there is a null value, then you'll end up with NULL instead of '0'. This probably isn't a problem, but something I've learnt the hard way.