MySQL 中的字符串格式化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/633668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
String formatting in MySQL
提问by Paul Oyster
Is there a printf()-like formatting in MySQL?
I couldn't find a straightforward way in the documentation.
MySQL 中是否有类似 printf() 的格式？
我在文档中找不到直接的方法。
For example, how can I make something like:
例如，我如何制作类似的东西：
SELECT STRFORMATFUNCTIONIMLOOKINGFOR("%03d", 17)
to get 017 ?
得到017？
采纳答案by Pierre
You can implement this kind of function by creating a new UDF (user defined function).E.g. see http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html
您可以通过创建新的 UDF（用户定义的函数）来实现这种功能。例如，请参阅http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html
回答by ax.
回答by V. Kovpak
Maybe it will be useful:
也许它会很有用：
select lpad(1, 2, '0') str;
+-----+
| str |
+-----+
| 01 |
+-----+
select lpad(11, 2, '0') str;
+-----+
| str |
+-----+
| 11 |
+-----+
回答by Sergei
see FORMAT() function:
参见 FORMAT() 函数：
mysql> SELECT FORMAT(12332.123456, 4);
returns '12,332.1235'
but it's only for formatting float numbers.
但它仅用于格式化浮点数。
回答by Ivan Sans?o
--
-- Autor: Ivan Sans?o.
--
-- Fun??o para formatar strings.
-- Exemplo: select mask("0123456789","(##) ####-####");
-- Retorna: (01) 2345-6789
CREATE FUNCTION mask(texto VARCHAR(255), mascara VARCHAR(255)) RETURNS VARCHAR(255) CHARSET latin1
Begin
-- Contém o texto processado.
DECLARE textoProcessado VARCHAR(255);
SET textoProcessado = "";
-- Se tem Texto.
IF length(texto) > 0 THEN
-- Percorre a máscara enquanto seu tamanho for maior que zero.
WHILE Length(mascara) > 0 DO
-- Se o caracter é um curinga.
IF LEFT(mascara,1) = "#" THEN
-- Concatena o primeiro caracter do texto.
SET textoProcessado = concat(textoProcessado,LEFT(texto,1));
-- Elimina o primeiro caracter da máscara.
SET mascara = SUBSTRING(mascara,2,255);
-- Elimina o primeiro caracter do texto.
SET texto = SUBSTRING(texto,2,255);
ELSE
-- Concatena o primeiro caracter da máscara.
SET textoProcessado = concat(textoProcessado,LEFT(mascara,1));
-- Elimina o primeiro caracter da máscara.
SET mascara = SUBSTRING(mascara,2,255);
END IF;
END WHILE;
END IF;
RETURN trim(textoProcessado);
END
