Click here to Skip to main content
16,018,802 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

A Simple Example of Microsoft SQL Server Pad String

Rate me:
Please Sign up or sign in to vote.
4.50/5 (9 votes)
18 Dec 2015CPOL 11.9K   5   5
A simple example of Microsoft SQL Server Pad String

Introduction

An easy way to Pad String using Microsoft SQL Server.

Using the Code

A sample code snippet is given below:

SQL
//
CREATE FUNCTION [dbo].[FUN_pad_string] 

(
	@p_seq VARCHAR(16),
	@p_pad_with CHAR(1),
	@p_pad_length INT
) 

RETURNS VARCHAR(16) AS

BEGIN 

DECLARE @g_current_seq varchar(16)

SELECT @g_current_seq = ISNULL(REPLICATE(@p_pad_with, @p_pad_length - LEN(ISNULL(@p_seq ,0))), '') + @p_seq

RETURN @g_current_seq

END
//

Conclusion

I hope you get the scenario and this might be helpful to you. Enjoy!

History

  • Saturday, December 19th, 2015: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralMy vote of 3 Pin
dmjm-h22-Dec-15 4:13
dmjm-h22-Dec-15 4:13 
QuestionA better version Pin
Darek Danielewski21-Dec-15 5:53
Darek Danielewski21-Dec-15 5:53 
Assuming that there is no need to check for the length of the string versus the desired length of padded string to be returned, as well as that the original should be trimmed, this will perform better and more accurately.

SQL
CREATE FUNCTION dbo.PADLEFT
  (
    @source NVARCHAR(4000) ,
    @pad NCHAR(1) ,
    @length INT
  )
RETURNS NVARCHAR(4000)
  BEGIN
    IF @source IS NULL
      OR @pad IS NULL
      RETURN NULL;
    RETURN RIGHT(CONCAT(REPLICATE(@pad, @length),LTRIM(RTRIM(@source))), @length);

  END;

AnswerRe: A better version Pin
Md. Marufuzzaman21-Dec-15 6:01
professionalMd. Marufuzzaman21-Dec-15 6:01 
GeneralMy vote of 1 Pin
Darek Danielewski21-Dec-15 5:35
Darek Danielewski21-Dec-15 5:35 
GeneralRe: My vote of 1 Pin
Md. Marufuzzaman21-Dec-15 6:00
professionalMd. Marufuzzaman21-Dec-15 6:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.