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? Show
Niko 26.2k8 gold badges93 silver badges109 bronze badges asked Oct 1, 2008 at 8:01
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: 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: 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 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: INSERT(str,pos,len,newstr) Where Replace a WordHere’s an example where I use SELECT INSERT('Cats and dogs', 6, 3, 'like');Result: Cats like dogs This effectively replaces the word Insert a WordHere I simply insert a word without deleting anything from the original string: SELECT INSERT('Cats and dogs', 10, 0, 'big ');Result: Cats and big dogs The reason this doesn’t delete anything from the original string is because I specified
Out of Range ValuesIf you specify a position that’s outside the length of the original string, MySQL will return the original string unchanged. Example: SELECT INSERT('Cats and dogs', 20, 4, 'rabbits');Result: Cats and dogs Here’s another example where I use a negative starting position: SELECT INSERT('Cats and dogs', -1, 4, 'rabbits');Result: Cats and dogs This is one of the differences between MySQL’s Inserting NULL ValuesAnother area where MySQL’s SELECT INSERT('Cats and dogs', 6, 3, NULL);Result: NULL How do I append a character to a string in MySQL?MySQL CONCAT() function is used to add two or more strings.. There may be one or more arguments.. Returns the string that results from concatenating the arguments.. Returns a nonbinary string, if all arguments are nonbinary strings.. Returns a binary string, if the arguments include any binary strings.. How do I change the first character of a string in MySQL?Use the MySQL REPLACE() function to replace a substring (i.e. words, a character, etc.) with another substring and return the changed string.
What is trim MySQL?MySQL TRIM() Function
The TRIM() function removes leading and trailing spaces from a string.
How do you add special characters to a database?MySQL - How to include special characters in a query. \0 - An ASCII NUL (0x00) character.. \' - A single quote ( ' ) character.. \" - A double quote ( " ) character.. \b - A backspace character.. \n - A newline (linefeed) character.. \r - A carriage return character.. \t - A tab character.. \Z - ASCII 26 (Control-Z).. |
