Home > Articles > Data > SQL

Practical SQL: Finding and Changing Words ("&" to "and")

  • Print
  • + Share This
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. You need a way to get inside the expression, find the offending segment, and change it, as shown in this article by Judith Bowman.
From the author of

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

 


  • + Share This
  • 🔖 Save To Your Account