NSC | SQL Essentials: SUBSTR Function Explained

Overview

In NetSuite Saved Searches, efficient data manipulation is key to extracting valuable insights. The SUBSTR function emerges as a fundamental tool in SQL for extracting substrings from strings based on specified criteria.

Understanding the Syntax

SUBSTR(string, start_position, length)

The SUBSTR function retrieves a portion of the 'string' starting from the 'start_position' and extending for a specified 'length'.

For example:
Within a NetSuite Saved Search, phone numbers are stored as a single string, and you need to extract area codes for analysis. The format of the phone numbers on the account is (123) 456-7890

Formula: SUBSTR({phone}, 2, 3)

This formula extracts the substring starting from the second character (to exclude the opening parenthesis) with a length of three characters, representing the area code.