CODEWITHSUNDEEP
sql-serversql-server-2017
ookie
ookie

Reputation: 138

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

Upvotes: 1

Views: 522

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

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)

Upvotes: 1

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @Name VARCHAR(12) = 'pName'
       ,@Price MONEY = 12;

DECLARE @HelpString VARCHAR(30) = REPLICATE('.', 30)

SELECT LEFT(@Name + @HelpString, 30), @Price

The idea is simple - concatenate your text with 30 . - then get first 30 symbols starting from the left side of the string.

Upvotes: 1

Related Questions