Position Expression
Wed, 03/04/2009 - 20:02 | 
hdanzl
Syntax
|
POSITION ( <string value expression> IN <string value expression> [ USING <char length units> ] ) |
|
POSITION ( <blob value expression> IN <blob value expression> ) |
|
| |
CHARACTERS OCTETS |
Notes
| ♦ | The POSITION function returns the ordinal position of the first occurrence of the first string expression within the second string expression. |
| ♦ | The USING clause shall only be specified if the IN expression is a UNICODE character string. CHARACTERS, which is implicit if the clause is omitted, returns the ordinal character position, while OCTETS returns the ordinal byte position. |
| ♦ | The POSITION function returns the ordinal byte position when the IN expression is a binary string. |
| ♦ | If the substring is not found within the second string, then 0 is returned. |
| ♦ | The data type of the result is INTEGER. |
| ♦ | If either of the arguments are null, then the result is null. |
Examples
1) The following example lists all courses starting with 'C':
SELECT *
FROM courses
WHERE POSITION( 'C' IN courseName ) = 1
| 2) | In the following example, the POSITION function returns 3 with the CHARACTERS char length unit implicitly specified: |
SELECT POSITION( 'a' IN N'Grace' )
FROM #dummy
| 3) | In the following example, the POSITION function returns 6 with the OCTETS char length unit explicitly specified: |
SELECT POSITION( 'a' IN N'Grace' USING OCTETS )
FROM #dummy
Conformance
|
SQL:2003 standard |
- |
Core SQL |
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
Site Updates
- Dec 4 2024 - 1:53pm
- Dec 4 2024 - 1:48pm
- Nov 19 2024 - 1:04pm
- Oct 17 2024 - 10:27am
- Jul 24 2024 - 1:22pm