SQL String Manipulation

Coding

SQL

LPAD

RPAD

Processing Numbers as Strings

CAST

TO_CHAR

Bibliography

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.

CNF Database

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

LPADhttps://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2017.htm
RPADhttps://docs.oracle.com/cd/B19306_01/server.102/b14200/functions140.htmhttps://www.techonthenet.com/mysql/functions/rpad.php
RIGHThttps://www.postgresqltutorial.com/postgresql-string-functions/postgresql-right/https://stackoverflow.com/questions/6230665/stored-procedure-to-select-last-6-digits-of-number
SUBSTRhttps://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm
SUBSTRINGhttps://www.w3resource.com/PostgreSQL/substring-function.php
INSTRhttps://www.w3schools.com/sql/func_mysql_instr.asphttps://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSTR.html
CASThttps://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/
TO_CHARhttps://www.postgresqltutorial.com/postgresql-string-functions/postgresql-to_char/
ODBChttps://nocolumnname.blog/2020/10/05/odc-date-time-extension-option-in-sql-server/https://docs.microsoft.com/en-us/sql/relational-databases/collations/write-international-transact-sql-statements?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql?view=sql-server-ver15

Page updated

Google Sites

Report abuse