About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.4.3
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1

Back Next

POSSTR

A string function that returns the starting position of a string value-expression1 within another string value-expression2. The Data Vault Service evaluates the position of the string from left to right, starting at position 1. If value-expression1 is not found, POSSTR returns 0. If value-expression1 occurs more than once within value-expression2, POSSTR returns the starting position of the first occurrence of value-expression1.

The POSSTR function is similar to the POSITION function. Both functions return the position of a substring within a string.

POSSTR Syntax

POSSTR (char value-expression2, char value-expression1)

POSSTR Example 1

In this example, value-expression1 occurs once within value-expression2. POSSTR returns the starting position of value-expression1.

Input	Output
'solstice', 'ice'	6

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('solstice');
1 row affected
SELECT POSSTR(col1, 'ice') FROM string_table;
1 row selected
1
-----------
          6

POSSTR Example 2

In this example, value-expression1 occurs more than once within value-expression2. POSSTR returns the starting position of the last occurrence of value-expression1.

Input	Output
'4234.23423', '4'	8

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('4234.23423');
1 row affected
SELECT POSSTR(col1, '4') FROM string_table;
1 row selected
1
-----------
          8

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

POSSTR

POSSTR

POSSTR Syntax

POSSTR Example 1

POSSTR Example 2