Search code examples
sql-serversql-server-2017

I am trying to add trailing '.' padding a string to a specific length

I am trying to pad a string that contains product and price. I have upgraded from MSSQL 2008 TO MSSQL 2017 (EXPRESS). I am unable to achieve the results that I was getting before.

In MSSQL 2008 I was using the following:

dbo.String.Padright(rtrim(p.Name_en),30,'.'), p.Price

Now, MSSQL 2017 this function does not work. I am unable to figure out how to pad with a character.

This is the output I am looking to achieve:

pName....................... 12.00

Solution

  • You can use right for leading and left for trailing dots functions, i used space() function to replicate .

    select right(replace(space(30), space(1), '.') + rtrim(cast(p.Name_en as varchar(max)),30), p.Price

    For trailing dots

    select left(trim(cast(p.Name_e as varchar(max)))+replace(space(30), space(1), '.'),30)