zero to left (LPAD) in Sql Server

Asked

Viewed 62,099 times

6

How is done in Sqlserver so that the result comes with zero left ?

in mysql would look like this:

select lpad(mes,2,0 ) as 'mes', ano from tab_qualquer;

How does something equivalent in Sql Server?

I’ve searched the https://stackoverflow.com/questions/5912416/lpad-in-sql-server-2008 and I did so:

select distinct
     right(replicate('0',2) + mes,2) as mes, 
     ano
from tabela

and made the following mistake:

Error code 0, state of SQL 22019: Unrecognized SQL escape 'year' at line position 702. Row 1, column 1

sql-server
  • 1

    This select would not return what you need, but it was also not to give error. Could you show how the structure of your table is? What types of month and year attributes

3 answers

12


If the mes field is numeric, to display with zero on the left, vc should demonstrate it as a string. Error may be masking the real problem. Pass the table structure.

select distinct
     right(replicate('0',2) + convert(VARCHAR,mes),2) as mes, 
     ano
from tabela

  • vlw, was the same Convert

3

FOLLOWS:

Select REPLICATE('0', 2 - LEN(PER_CODIGO)) + RTrim(PER_CODIGO) FROM PERFIL

  • '0' is the character you want to repeat,

  • 2 is number of houses of your string,

  • 'PER_CODIGO' is your field.

0

If you want the example below it works very well.

select RIGHT('000' + CAST('025' AS VARCHAR(10)),8)

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.