SQL: INSERT Statement vs. INSERT() Function


Twins they are not.

Just because they look alike...

Generally I am a fan of SQL and how it works, but there is a function that I find irritating. Not because of what it does, but because of its name, “INSERT()”. Why, oh why would you give a function the same name as a statement that already exists? I am sure there is a story behind this and it probably goes back to family squabbles occuring in the 10th century AD, or at least to the 1970s when Chamberlin and Boyce first conceived RDBMS. Suffice it to say that it could cause confusion when quickly glancing over code or when newcomers are learning the language.

Just know that the two are different. The statement is the familiar, much used instruction to the database to add a row to a table.

INSERT INTO table (column1, column2, etc.)
VALUES(‘value1′, ‘value2′, ‘etc.’)

Meanwhile, the INSERT() function is designed to replace text within a string. It does this using the following syntax:

INSERT(‘string’, position, length, ‘inserted_string’)

The first parameter is the string to be operated upon. The position is where the insertion should begin. Length defines how many characters should be replaced. The fourth parameter is the string to be injected.

A simple example:

SELECT INSERT(‘Jane Porter was the heroine in Harry Potter.’, 32, 12, ‘Tarzan’)

Our result, “Jane Porter was the heroine in Tarzan.” Note that the character specified in the second parameter is over-written as are the subsequent 11 characters, 12 characters in all.

  1. No comments yet.

You must be logged in to post a comment.