SQL String Manipulation

LPAD

INSERT INTO myTable(myCol) VALUES ('ABCDEFG');

SELECT LPAD(myCol,10,'*') FROM myTABLE;

***ABCDEFG

RPAD

This syntax valid for: Oracle, MySQL

INSERT INTO myTable(myCol) VALUES ('ABCDEFG');

SELECT RPAD(myCol,10,'*') FROM myTABLE;

ABCDEFG***

SUBSTR

INSERT INTO myTable(myCol) VALUES ('ABCDEFG');

SELECT SUBSTR(myCol,2,3) FROM myTable;

BCD

SELECT SUBSTR(myCol,-4,3) FROM myTable;

DEF

SUBSTRING

This syntax valid for: PostgreSQL

INSERT INTO myTable(myCol) VALUES ('ABCDEFG');

SELECT SUBSTR(myCol,2,3) FROM myTable;

BCD

SELECT SUBSTR(myCol,-4,3) FROM myTable;

DEF

INSTR

This syntax valid for: Oracle, MySQL

SELECT *

  FROM myTable

 WHERE INSTR(myCol,'mystring');

RIGHT

This syntax valid for: PostgreSQL

MOD(CAST(SUBSTR(RIGHT(CAST(myinteger AS VARCHAR),6),1,3) AS INTEGER),250) 

Takes the first 3 digits of the last 6 digits of an integer and applies a modulo function to it.CASTing from INTEGER-VARCHAR-INTEGER is required as RIGHT is a string function.
The CNF Database page contains a good example.

Processing Numbers as Strings

CAST

CAST(myinteger AS VARCHAR)

CAST(myStringOfNumbers AS INTEGER)

TO_CHAR

SELECT TO_CHAR(6542,'9999');

6542

SELECT TO_CHAR(6542,'9,999');

6,542

Bibliography