mysql query to insert 0's
-
Hi, I have a unique id column in a table with values - 12 32 45 345 3534 45456 etc. Now i want to make all the values to be 7 digit by placing 0's in front of each id for example above ids will be 0000012 0000032 0000045 0000345 0003534 0045456 Now there are 1000's of values that i need to change - what would be the best possible way to write query for this. Thanks
LPAD(my_col, 7, '0')
-
Hi, I have a unique id column in a table with values - 12 32 45 345 3534 45456 etc. Now i want to make all the values to be 7 digit by placing 0's in front of each id for example above ids will be 0000012 0000032 0000045 0000345 0003534 0045456 Now there are 1000's of values that i need to change - what would be the best possible way to write query for this. Thanks
That would only work if they're string values, which they shouldn't be. You could left pad them when you display them.
-
That would only work if they're string values, which they shouldn't be. You could left pad them when you display them.
-
AndyInUK wrote:
yeh they are int values. Anyway to do it for int ?
Why would you want to? The following values are all the same: 1 01 001 0001 Generally, you'll apply padding purely for display/writing to file purposes, and this should be accomplished in the client code - not in the database.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
That would only work if they're string values, which they shouldn't be. You could left pad them when you display them.
True enough. I assumed they were strings because the question doesn't really make sense if they are numeric.
-
AndyInUK wrote:
yeh they are int values. Anyway to do it for int ?
Why would you want to? The following values are all the same: 1 01 001 0001 Generally, you'll apply padding purely for display/writing to file purposes, and this should be accomplished in the client code - not in the database.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
yes they mean same but when we try to sort its meaning will change thats y for sorting we need this
-
Can you explain? Why do you need to have preceding zeroes in a number to sort? Simply put, if it is an integer, then 1. you cannot do it 2. you do not need it
-
because without zeroes its not sorting in ascending like i have data 1 2 3 10 11 21 31 without zeroes its sorting like this 1 11 21 31 2 3
-
because without zeroes its not sorting in ascending like i have data 1 2 3 10 11 21 31 without zeroes its sorting like this 1 11 21 31 2 3
As Danish said, this is a text sort, you need to check your data type.
Never underestimate the power of human stupidity RAH
-
because without zeroes its not sorting in ascending like i have data 1 2 3 10 11 21 31 without zeroes its sorting like this 1 11 21 31 2 3
eraser950 wrote:
because without zeroes its not sorting in ascending like i have data 1 2 3 10 11 21 31 without zeroes its sorting like this 1 11 21 31 2 3
That means they are stored as text, and not numbers. :rolleyes: Sorting numeric data types gives you the values in the right order.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
Hi, I have a unique id column in a table with values - 12 32 45 345 3534 45456 etc. Now i want to make all the values to be 7 digit by placing 0's in front of each id for example above ids will be 0000012 0000032 0000045 0000345 0003534 0045456 Now there are 1000's of values that i need to change - what would be the best possible way to write query for this. Thanks
I assume mysql has similar sorts of functions but I have not had the pleasure, but in T-SQL / SQL Server you could do: select ID = right('0000000' + convert(varchar(20),ID),7) from MyTable -- ID can be int or varchar or select ID = right('0000000' + ID,7) from MyTable -- OK only when ID is varchar. Likewise you could put this into an update statement if for example you are in the process of converting your ID column from type int to varchar/text etc.