Systems Engineering and RDBMS

Padding Strings with characters

Posted by decipherinfosys on March 16, 2007

If you want to manipulate a string by padding it with special characters, you can do it by following these steps:

First we will create the table. Following syntax should work for all three RDBMS that we cover on our blog (Oracle, SQL Server and DB2 LUW).

CREATE TABLE PADTEST(COL1 VARCHAR(10));

INSERT INTO PADTEST VALUES(‘DECIPHER’);
INSERT INTO PADTEST VALUES(‘INFO’);
INSERT INTO PADTEST VALUES(‘SYS’);

Oracle provides two functions. LPAD is used to pad extra characters in the beginning of the string and RPAD is used to pad extra characters at the end of the string. Following is the example of left padding the string with ‘0’ till the length of the result string becomes 10.

SQL> SELECT LPAD(col1,10,’0′) as NAME FROM PADTEST;

NAME
———-
00DECIPHER
000000INFO
0000000SYS

If character is not specified to pad the string, single blank is taken as default value. Here is an example.

SQL> SELECT LPAD(col1,10) as NAME FROM PADTEST;

NAME
———-
DECIPHER
INFO
SYS

RPAD also works in a same way except it appends the string at the end. So if RPAD is used instead of LPAD in first query, string will be followed by ‘0’s. For detailed syntax of LPAD/RPAD, please refer to Oracle’s SQL Reference manual.

MS SQL SERVER does not have LPAD or RPAD function but we can mimic the functionality close to LPAD/RPAD using REPLICATE command. Create the same table shown above in sqlserver database. Execute following statement to see the same effect as LPAD.

SELECT REPLICATE(’0′, 10 – DATALENGTH(col1)) + col1 AS NAME
FROM PADTEST
GO

NAME
———
00DECIPHER
000000INFO
0000000SYS

For RPAD, we have to use following syntax.

SELECT col1 + REPLICATE(’0′, 10 – DATALENGTH(col1)) AS NAME
FROM PADTEST
GO

NAME
———-
DECIPHER00
INFO000000
SYS0000000

Please make sure that length to replicate specific character is positive. If length is negative then it will return NULL instead of appended string. For detailed syntax of REPLICATE command, please refer to BOL.

DB2 LUW also doesn’t have LPAD or RPAD commands. In DB2 we can replicate the same functionality using REPEAT or RIGHT command. Here also we will try to append ‘0’s to the existing string to make it look uniform. Create the same table mentioned in the beginning of the article. Make sure that statement terminator is defined as ‘;’. Run following command to left pad the string.

SELECT REPEAT(’0′,10-LENGTH(col1)) || col1 as NAME
FROM PADTEST;

NAME
———
00DECIPHER
000000INFO
0000000SYS

Or

we can also use RIGHT function to get the number of rightmost characters in the string.  SQL statement using RIGHT function will be as shown bellow.

SELECT RIGHT(’0000000000′||COL1,10) AS NAME
FROM PADTEST;

NAME
———
00DECIPHER
000000INFO
0000000SYS

Here we are first concatenating existing string with fixed length(10) string of 0′s. So that when function is invoked to return rightmost 10 characters, string already contains leading 0s.

In DB2 we can use REPEAT or RIGHT functions to closely mimic the LPAD and RPAD functionality of oracle. Above example will work fine when length to replicate the string is more than the total length of the string. If length to replicate the string is less than the total length of the string, LPAD or RPAD truncates the string to the given length. In order to achieve same in DB2, we will have to do some more string manipulation.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: