- 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
Using POSITION Functions
Because it finds a string inside another string, and returns its starting place as a number, POSITION (by any name) is useful as input to other functions. The code below uses the LOCATE version of POSITION as the START argument of a function that retrieves a specified part of a string. The function is usually called SUBSTR or SUBSTRING. Before looking at the LOCATE-SUBSTRING code, give SUBSTR/ING a glance.
Choosing a Subset
This useful character function gives you access to a subset of a character column. It takes three arguments: the expression, the start of the substring, and the size of the substring. See Table 2 for a list of variants. Notice that Informix supports three versions.
Table 2[em]Substring Functions
|
ANSI |
ASA |
MS SQL Server |
Oracle |
Informix |
|
SUBSTRING (char_expr FROM start [FOR size]) SUBSTRING (char_expr FROM start [ FOR size ] ) |
SUBSTR (char_expr, start [, size]) SUBSTRING (char_expr, start, size) |
SUBSTRING (char_expr, start, size) |
SUBSTR (char_expr, start [, size]) |
SUBSTRING (char_expr FROM start FOR size) SUBSTR (char_expr, start [, size]) char_expr [start, end] |
When you're experimenting with substring functions, check to see if your system allows an optional third argument. If no third argument is required, and you leave it out, the SQL engine displays all characters after the start value. Systems that can do without the third argument may accept a negative second argument as instruction to count backward from the end of the expression for that number of characters.
For example, you want to use the last four digits of each employee number as the employee's default key code for the new electronic locks. Here are three ways you could get the number (the second and third versions require an optional last argument):
By starting at character 6 of the expression and going forward four characters
By starting at character 6 of the expression and retrieving all following characters
By starting at the end of the expression and going backward for four characters
select fname, empnum, substr (empnum, 6, 4) as ThreeArgs, substr (empnum, 6 ) as CountFwd, substr (empnum, -4 ) as CountBack from employee fname empnum ThreeArgs CountFwd CountBack ==================== ========= ========= ========= ========= Hamid 123232345 2345 2345 2345 laurna 223232366 2366 2366 2366 ruby 111223333 3333 3333 3333 lauren 923457789 7789 7789 7789 Bill 222222221 2221 2221 2221 Scorley 443232366 2366 2366 2366 [6 rows]
Another variation in substring functions is the meaning of a zero (0) starting position. Most systems treat 0 and 1 the same: Both mean "start from the first character." Transact-SQL does not. Instead, it interprets 0 as "Don't start the substring" and 1 as "Start from the first character."
Testing Changes with POSITION (LOCATE) and SUBSTR/ING
Here's an example of using LOCATE as an argument to SUBSTR. You need to substitute the word "and" for every ampersand ("&") you find in product names—a new business rule disallows single characters as shortcuts for words. Start by testing your logic in a SELECT. When you get the results you want, use an UPDATE to make the changes.
Use LOCATE to find the &. Then use the value of the ampersand location -1 as the SIZE parameter of a SUBSTR. Specify 1 as the START value. That translates as "Start at the first character in name, and stop at one character before the &."
In the same way, use the location of the ampersand as the START value of another substring. Because the ASA version of SUBSTR/ING does not require a third argument, the code segment reads "Start at the first character after the ampersand in name, and go to the end of name."
Put the two strings together, with the word "and" between them. The double pipe (||) is the ANSI SQL concatenate operator, but some dialects support the plus sign (+). Now you have the text before the ampersand, the word "and", and the text after the ampersand.
select name,
substr (name, 1, locate( name, '&')-1)
|| 'and' ||
substr (name, locate( name, '&') +1)
from product
where name like '%&%'
name substr("name",1,locate("name"
==================== ============================================
cook & book cook and book
blood & guts blood and guts
[2 rows]
You found two instances, and both substituted "and" for "&", as you expected.
Changing Data
To change your data, translate the SELECT into an UPDATE—and don't forget the WHERE clause. If you want to make the change reversible, put it inside a transaction. In this dialect, run a BEGIN TRAN before the UPDATE, and do a ROLLBACK after you check your results.
begin tran update product set name = substr (name, 1, locate( name, '&')-1) || 'and' || substr (name, locate( name, '&') +1) where name like '%&%' [2 rows updated] select name from product where name like '% and%' name ==================== cook and book blood and guts [2 rows] rollback select name from product where name like '% and%' or name like '%&%' name ==================== cook & book blood & guts [2 rows]
Caveats and Variations
Notice that the LOCATE finds the first &. If there were more than one, you'd have to run the code multiple times. The Oracle version of this function (INSTR) takes four arguments, and allows you to specify a starting point and occurrence number, so that you could look for a character in positions four to ten, or the third occurrence of a character.