Practical SQL: Finding and Changing Words ("&" to "and")
- String In String Functions
- How POSITION Works
- Using POSITION Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Sometimes a special character used in one SQL dialect is illegal in another, or a word that occurs in random positions within a column needs to be changed. A simple match won't work. You need a way to get inside the expression, find the offending segment, and change it. This article, based on material in Practical SQL: The Sequel (Addison-Wesley), shows one method for solving the problem. Unless otherwise noted, code is run on Sybase's small footprint Adaptive Server Anywhere (ASA).
String In String Functions
Most relational database management system (RDBMS) vendors have a SQL character function that reports where one string occurs inside another. ANSI calls it POSITION, but vendors use a wide variety of names. For a list of POSITION equivalents in four popular RDBMSs, see Table 1. Whatever its name, the function tells you two things:
Whether or not the match expression exists in the target expression
Where the match expression begins in the target expression
In sum, POSITION (or LOCATE/CHARINDEX/INSTR...) returns a number that tells you where one expression starts inside another. This means you can use this function to find a pattern (as LIKE does), and pass on the location of the pattern to another function (as LIKE does not do).
Table 1[em]POSITION and Near Relatives
|
ANSI |
Sybase Adaptive Server Anywhere |
MS SQL Server |
Oracle |
Informix |
|
POSITION POSITION |
LOCATE CHARINDEX |
CHARINDEX |
INSTR |
|