-
SQL Essentials: Regular Expressions Explained
Overview Regular Expressions, often abbreviated as Regex, is a powerful tool in Saved Search Formulas that allows you to search and manipulate text using pattern matching. It provides a flexible way to find, replace, or extract specific text patterns within your data. In simple terms, it's like a search-and-replace…
-
SQL Essentials: REGEXP_REPLACE Function Explained
Overview REGEXP_REPLACE is a function in Saved Search Formulas that enables users to manipulate text using regular expressions, offering a powerful tool for data transformation and extraction. The function's basic purpose is to search and replace text within a string based on a specified pattern. However, the real…
-
SQL Essentials: REGEXP_INSTR Function Explained
Overview REGEXP_INSTR is a text-searching function in Saved Search Formulas that uses regular expressions to find the position of a substring within a text field. It returns the starting position of the first occurrence of the specified pattern in the text. This function is particularly useful when you need to locate…
-
SQL Essentials: REGEXP_SUBSTR Function Explained
Overview REGEXP_SUBSTR is a string function that allows you to extract substrings from a text field based on a regular expression pattern. Regular expressions, often abbreviated as regex, are a sequence of characters that define a search pattern. This function is particularly useful when you need to search for specific…
-
NSC | Saved Search Formula to Show Same Value in NS_CONCAT with DISTINCT Formula
Scenario: A user is working with a Project Saved Search, specifically focusing on extracting unique Sales Orders and their corresponding Total Amounts. The initial approach involved using a formula to concatenate the Sales Order number and Amount, ensuring uniqueness by combining them into a single string. Initial Formula:…
-
NSC | Saved Search Formula to Identify Specific Format of Phone Numbers
Scenario User needs to identify customer records where the phone numbers stored in a NetSuite field do not adhere to the format "###-###-####". This might happen due to manual data entry errors, lack of data validation and/or changes in formatting standards. Solution Use the REGEXP_SUBSTR function to check if the format is…
-
NSC | REGEXP Formula to Get Email Domain
Scenario User wants to create a Saved Search to extract the domain names from customer email addresses in order to analyze the distribution of email domains among its customer base to better understand its market demographics and tailor marketing strategies accordingly. Solution REGEXP_SUBSTR formula can be used in Saved…
-
NSC | REGEXP Formula for name format: ABC-1234
Scenario User needs to create a Saved Search to identify all products whose SKUs follow a specific format: "ABC-1234" The first 3 characters are 3 upper case letters Followed by a hyphen and the last 4 characters are 4 digits Solution Use a REGEXP formula in your Saved Search Criteria to filter items based on the SKU…
-
NSC | Using Special Characters in REGEXP Formula Patterns
Scenario User needs to use a special character (such as period, asterisk, plus sign) in a REGEXP formula. In REGEXP, special characters are used to define patterns for searching and matching within strings. For example: Period (.): Matches any single character except newline. Asterisk (*): Matches zero or more occurrences…
-
NSC | Saved Search Formula to get a String in the Value of a Field
Scenario User has the following value in entityid field: abcde fghijk(53qw4ertyuiop12345a67s89) They need to pull just the 53qw4ertyuiop12345a67s89 from that string/pattern. Solution Use either of these in a Formula (Text) field in the search Results: REPLACE(REGEXP_SUBSTR({entityid},'[^(]+$'),')','') Using this will pull…