Result Value

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

SUBSTRING/SUBSTR extracts n2 characters or bytes from string_expression starting at position n1.

To get the number of characters or bytes in the resulting string, use the BYTE function for byte strings and the CHARACTER_LENGTH function for character strings.

If either of the following conditions are true, SUBSTRING/SUBSTR returns a zero length string:
  • (n1 > string_length) AND (0 ≤ n2)
  • (n1 < 1) AND (0 ≤ n2) AND ((n2 + n1 - 1) ≤ 0)
IF n2 is … THEN …
specified  
  IF … THEN …  
  n2 < 0 SUBSTRING/SUBSTR returns an error.  
  0 ≤ n2 and

n1 >

string_length
SUBSTRING/SUBSTR returns a string containing zero characters.  
  0 ≤ n2 and

n1 < 1

SUBSTRING/SUBSTR sets n4  = n2  + n1  - 1 and sets n3  = 1.  
    IF … THEN SUBSTRING/ SUBSTR returns …    
    n4 ≤ 0 a string containing zero characters.    
    n4 > string_length the source string.    
    0 < n4 <= string_length a string that starts at n3 and extends for n4 characters.    
     
  0 < n2

AND

1 ≤ n1 ≤ string_length

   
    IF … THEN SUBSTRING/ SUBSTR returns a string …    
    (n1 + n2 - 1) > string_length that starts at n1 and ends with the last character of the source string.    
    0 < (n1 + n2 - 1) ≤ string_length that starts at n1 and extends for n2 characters.    
     
 
not specified  
  IF … THEN SUBSTRING/SUBSTR returns …  
  n1 < 1 the source string.

If the source string is a CHAR type, trailing pad characters are trimmed.

 
  n1 > string_length a string containing zero characters.  
  1 ≤ n1 ≤ string_length a string that starts at n1 and ends with the last character of the source string.

If the source string is a CHAR type, trailing pad characters are trimmed.