Comprehensive Guide to String Concatenation with Padding in SQLite

Keywords: SQLite | String Concatenation | Padding Functions | Data Formatting | substr Function

Abstract: This article provides an in-depth exploration of string concatenation and padding techniques in SQLite databases. By analyzing the combination of SQLite's string concatenation operator || and substr function, it details how to implement padding functionality similar to lpad and rpad. The article includes complete code examples and step-by-step explanations, demonstrating how to format multiple column data into standardized string outputs like A-01-0001.

Fundamentals of String Operations in SQLite

In SQLite databases, string concatenation is primarily achieved through the || operator. This operator functions by joining two string operands together to form a new string. For instance, the expression 'Hello' || 'World' returns 'HelloWorld'.

Implementation Principles of Padding Functions

Since SQLite does not natively support lpad and rpad functions, padding functionality must be implemented by combining string concatenation with substring extraction. The basic approach involves: first creating a string containing sufficient padding characters, then concatenating it with the target string, and finally using the substr function to extract the portion of desired length.

Left Padding Implementation Method

The implementation code for left padding is as follows:

SELECT substr('0000000000' || mycolumn, -10, 10) FROM mytable

This code functions equivalently to an lpad function:

'0000000000' is a padding string consisting of 10 zeros
mycolumn is the target column requiring padding
The -10 parameter indicates starting from 10 characters before the end of the string
The 10 parameter specifies extracting 10 characters in length

Right Padding Implementation Method

The implementation code for right padding is as follows:

SELECT substr(mycolumn || '0000000000', 1, 10) FROM mytable

This code functions equivalently to an rpad function:

mycolumn is concatenated with 10 zeros
The 1 parameter indicates starting from the first character
The 10 parameter specifies extracting 10 characters in length

Comprehensive Application Example

For the original problem of formatting three columns of data, the complete solution is:

SELECT col1 || '-' || substr('00'||col2, -2, 2) || '-' || substr('0000'||col3, -4, 4) FROM table

Code analysis:

col1 is used directly without padding
substr('00'||col2, -2, 2) ensures col2 always displays as 2-digit numbers, padding with leading zeros when necessary
substr('0000'||col3, -4, 4) ensures col3 always displays as 4-digit numbers, padding with leading zeros when necessary
Each part is connected with hyphen characters -

Execution Result Analysis

After applying the above query, the original data:

Column1    Column2    Column3
A          1          1
A          1          2
A          12         2
C          13         2
B          11         2

Will be formatted as:

A-01-0001
A-01-0002
A-12-0002
C-13-0002
B-11-0002

Technical Summary

The advantages of this method include:

Full utilization of SQLite built-in functions, no extensions required
Concise and understandable code, easy to maintain
Good performance, suitable for large data volume processing
High flexibility, adjustable padding length and characters according to requirements

It is important to note that the length of padding strings should be determined based on the maximum digits required by actual needs to ensure all data can be correctly formatted.

Copyright Notice: All rights in this article are reserved by the operators of DevGex. Reasonable sharing and citation are welcome; any reproduction, excerpting, or re-publication without prior permission is prohibited.