I have a table of users which has a username column consisting of a six digit number e.g 675381, I need to prepend 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?
Niko
26.2k8 gold badges93 silver badges109 bronze badges
asked Oct 1, 2008 at 8:01
UPDATE Tablename SET Username = Concat('0', Username);
Niko
26.2k8 gold badges93 silver badges109 bronze badges
answered Oct 1, 2008 at 8:06
danielsdaniels
18k30 gold badges100 silver badges170 bronze badges
0
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);answered Oct 1, 2008 at 8:07
f13of13o
4362 silver badges11 bronze badges
0
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.
answered Oct 1, 2008 at 8:09
Mike WoodhouseMike Woodhouse
51k12 gold badges88 silver badges126 bronze badges
0
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.
answered Oct 1, 2008 at 22:43
Darryl HeinDarryl Hein
140k89 gold badges213 silver badges259 bronze badges
In MySQL, you can use the INSERT() function to insert a string into another string. You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with
which to insert the new string, the number of characters to delete from the original string, and the new string to insert. Here’s the syntax: Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert. Here’s an example where I use INSERT() to replace a word within a string: Result:
This effectively replaces the word and with the word like. I used 6 because the word and started at the 6 character mark, and I used 3 because that’s how many characters I want to delete (the word and is 3 characters long). Here I simply insert a word without deleting anything from the original string: Result: The reason this doesn’t delete anything from the original string is because I specified
0 (which means zero characters should be deleted). If you specify a position that’s outside the length of the original string, MySQL will return the original string unchanged. Example: Result: Here’s another example where I use a negative starting position: Result: This is one of the differences between MySQL’s INSERT() function and
Transact-SQL‘s STUFF() function. In T-SQL, the STUFF() function would return NULL in these cases. Another area where MySQL’s INSERT() differs to T-SQL’s STUFF() is with NULL values. If you try to insert a NULL value, MySQL will return NULL. Result:Replace a Word
Insert a Word
Out of Range Values
Inserting NULL Values