CODEWITHSUNDEEP
mysqlsqlformattingformat
Paul Oyster
Paul Oyster

Reputation: 1248

String formatting in MySQL

Is there a printf()-like formatting in MySQL?
I couldn't find a straightforward way in the documentation.

For example, how can I make something like: 

SELECT STRFORMATFUNCTIONIMLOOKINGFOR("%03d", 17)

to get 017 ?

Upvotes: 8

Views: 30613

Answers (5)

cn0047
cn0047

Reputation: 17061

Maybe it will be useful:

select lpad(1, 2, '0') str;
+-----+
| str |
+-----+
| 01  | 
+-----+
select lpad(11, 2, '0') str;
+-----+
| str |
+-----+
| 11  | 
+-----+

Upvotes: 3

ax.
ax.

Reputation: 59927

for your example, you could use

SELECT LPAD(17, 3, '0');

there is also

SELECT FORMAT(17, 3); -- 17.000

otherwise, UDF as mentioned above.

Upvotes: 5

Ivan Sansão
Ivan Sansão

Reputation: 21

-- 
-- 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

Upvotes: 2

Sergei
Sergei

Reputation: 2757

see FORMAT() function:

mysql> SELECT FORMAT(12332.123456, 4);
returns '12,332.1235'

but it's only for formatting float numbers.

Upvotes: 2

Pierre
Pierre

Reputation: 35246

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

Upvotes: 0

Related Questions