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 HotFix 1
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3

Back Next

SUBSTRING

A string function that returns the specified portion of the input expression. Value-expression
is the input character string; start-position
is the starting position of the substring, counting from the left (the first character in the input string is position 1, the second character is position 2, and so on); and length
is the number of characters to return, counting from left to right from the substring starting position. If the length argument is omitted, the substring from the starting position to the end of the input string is returned.

SUBSTRING Syntax

 SUBSTRING ( char value-expression, integer start-position [, integer length ] )
 SUBSTRING ( char value-expression FROM integer start-position [ FOR integer length ] )
 SUBSTR ( char value-expression, integer start-position [, integer length ] )
SUBSTR ( char value-expression FROM integer start-position [ FOR integer length ] )

SUBSTRING Examples

Input	Output
'12345', 2, 2	'23'
'PK1-H4V1Y9-QC', 5, 6	'H4V1Y9'
'514-939-3477', 5	'939-3477'

CREATE TABLE string_table (col1 SMALLINT, col2 VARCHAR(20));
INSERT INTO string_table VALUES (1, '12345');
1 row affected
INSERT INTO string_table VALUES (2, 'PK1-H4V1Y9-QC');
1 row affected
INSERT INTO string_table VALUES (3, '514-939-3477');
1 row affected
SELECT SUBSTR(col2, 2, 2) FROM string_table WHERE col1=1;
1 row selected
1
--
23
SELECT SUBSTRING(col2 FROM 5 FOR 6) FROM string_table WHERE col1=2;
1 row selected

1
------
H4V1Y9
SELECT SUBSTR(col2, 5) FROM string_table WHERE col1=3;
1 row selected
1
----------------
939-3477

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

SUBSTRING

SUBSTRING

SUBSTRING Syntax

SUBSTRING Examples