CODEWITHSUNDEEP
sqlstring
CurtisHx
CurtisHx

Reputation: 758

Adding leading characters to string in SQL

I have a stored procedure. I pass in 1 variable, called @p_FR, which is a char(6). However, the passed value is not always 6 characters long. Sometimes the value is only 4 or 5 characters long. I need to make sure that the string is always 6 characters long. For example, if @p_FR is 8534, then I need to add 2 leading 0's to @p_FR, so it would be 008534.

I tried doing 

set @fr = right('000000', @p_FR, 6)

@fr is a varchar(6)

but when @p_FR is 8534, @fr is still 8534. I need it to be 008534.

I also set the following block of code in a fresh query, and I still got 8534.

declare @p_FR as char(6),
    @fr as varchar(6)
set @p_FR = '8534'
set @fr = right('000000' + @p_FR, 6) 
select @fr

Upvotes: 2

Views: 1916

Answers (2)

bjorsig
bjorsig

Reputation: 1107

I think the easiest way to solve the problem would be to use varchar(6) instead of char(6) by doing so you don't have to worry about trailing spaces since varchar only stores the string and doesn't include trailing spaces to fill up.

If that is not an option then you can use RTRIM(@p_FR) assuming your RDBMS supports RTRIM. You didn't specify what RDBMS you are using but I am assuming SQL Server of some sort.

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Use rtrim to remove blanks:

declare @p_FR as char(6),
    @fr as varchar(6)
set @p_FR = '8534'
set @fr = right('000000' + rtrim(@p_FR), 6) 
select @fr

Upvotes: 4

Related Questions