Is there a way to replace all number's digits to "0" but keep only first digit:
Sample convert:
`3849` => `3000`
`3001` => `3000`
`3838383838` => `3000000000`
and apply this on mysql column?
Thanks,
Add a comment
|
8 Answers
One straightforward option is to concatenate together the first digit with a sequence of zeroes whose length is one less than the length of the number.
SELECT
CONCAT(LEFT(col, 1), REPEAT('0', CHAR_LENGTH(col) - 1)) AS new_col
FROM yourTable
Note that if col be a numeric type column, then technically we should be casting it to text before taking a substring. In this case, MySQL would do an implicit conversion to text, so technically we don't need a formal cast, though this point should be made.
Demo here:
Rextester
1 Comment
markusk
Instead of
CONCAT(LEFT(col, 1), REPEAT('0', CHAR_LENGTH(col) - 1)), you can do RPAD(LEFT(col, 1), '0', CHAR_LENGTH(col)).hope this work for you
select left(column_name,1),
rpad(left(column_name,1),length(column_name),'0')
from table_name;
Comments
This is a C language style approach. Floor(3456/1000) gives us 3 (like an integer division), and just multiply with 1000 to get the result.
SELECT floor(3456 / power(10, length(3456)-1)) * power(10, length(3456)-1)
Comments
TRUNCATE with a negative second argument will set digits to the left of the decimal point to zero:
SELECT TRUNCATE(column_name, -FLOOR(LOG10(column_name)))
FROM table_name;
1 Comment
Tim Biegeleisen
Another good option +1. Tested and functioning.
select floor(n / POW(10, floor(LOG10(n)))) * POW(10, floor(LOG10(n))) from t
Comments
Try this:
SELECT left(columnname,1), rpad(left(columnname,1),length(columnname),'0') FROM tablename;
Comments
Can be done like this:-
select Column1-convert(bigint, (SUBSTRING(convert(varchar(20),Column1),2,len(convert(varchar(20),Column1))))) ,*
from yourtable
Comments
Try this for MYSql:
DECLARE v_test NVARCHAR(100)
SET v_test = '585623658'
SELECT LEFT(v_test,1) + REPLICATE(0,(CHAR_LENGTH(RTRIM(v_test)) - 1)) AS Colum
1 Comment
Tim Biegeleisen
This is SQL Server code. The question is tagged for MySQL.