{"id":817,"date":"2025-06-09T12:18:06","date_gmt":"2025-06-09T09:18:06","guid":{"rendered":"https:\/\/www.certbolt.com\/certification\/?p=817"},"modified":"2026-01-01T12:20:47","modified_gmt":"2026-01-01T09:20:47","slug":"how-to-use-sql-substring-to-extract-specific-characters-efficiently","status":"publish","type":"post","link":"https:\/\/www.certbolt.com\/certification\/how-to-use-sql-substring-to-extract-specific-characters-efficiently\/","title":{"rendered":"How to Use SQL Substring to Extract Specific Characters Efficiently"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Every data professional approaches the task of retrieving information differently, depending on the specific needs of their analysis or application. SQL, or Structured Query Language, offers a range of tools that help users extract and manipulate data efficiently. One of the fundamental functions available in SQL for handling strings is the substring function. This function provides a powerful way to extract a specific set of characters from a larger string, allowing for precise data manipulation and retrieval.<\/span><\/p>\n<p><b>What Is the Substring Function in SQL?<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The substring function in SQL is used to extract a portion of a string. This portion is called a substring, which is essentially a smaller segment derived from a longer text string. The substring function can work directly on literal strings specified within the query or on string data stored in the columns of database tables. By using a substring, users can retrieve characters starting from any position within a string, making it an essential tool for string processing and data cleaning tasks.<\/span><\/p>\n<p><b>Importance of Substring in Data Handling<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Extracting substrings is a common requirement in data handling and analysis. For example, you may need to extract area codes from phone numbers, first names from full names, or specific codes embedded in strings of characters. The substring function enables these operations without needing to export data into external software for processing. This efficiency makes it valuable for writing optimized SQL queries that run directly on the database server.<\/span><\/p>\n<p><b>Syntax of the SQL Substring Function<\/b><\/p>\n<p><b>Basic Syntax Structure<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The basic syntax of the substring function in SQL is as follows:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(string, start, length)<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>String<\/b><span style=\"font-weight: 400;\">: This parameter represents the input string from which you want to extract a substring. It can be a literal string or a column name.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Start<\/b><span style=\"font-weight: 400;\">: This parameter specifies the starting position within the string where extraction begins. It can be a positive or negative integer.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Length<\/b><span style=\"font-weight: 400;\">: This optional parameter defines the number of characters to extract starting from the position indicated by the start parameter. If omitted, the substring will extend from the start position to the end of the string.<\/span><\/li>\n<\/ul>\n<p><b>Understanding the Parameters<\/b><\/p>\n<p><b>The String Parameter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The string parameter is the source text from which the substring is extracted. This can be a direct string enclosed in single quotes, such as &#8216;HELLO WORLD&#8217;, or it can be a column from a table containing text data.<\/span><\/p>\n<p><b>The Start Parameter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The start parameter determines where the extraction begins within the string. When using a positive integer, counting starts at the beginning of the string, with the first character indexed as 1. When a negative integer is used, counting starts from the end of the string, where -1 refers to the last character, -2 the second last, and so on. This flexibility allows for easy extraction of substrings based on positions relative to either end of the string.<\/span><\/p>\n<p><b>The Length Parameter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The length parameter specifies how many characters to extract. This must be a positive number. If this parameter is omitted, the substring function extracts all characters from the start position to the end of the string. This optionality is useful when the full remaining substring is needed without specifying an exact length.<\/span><\/p>\n<p><b>Examples of Basic Syntax Usage<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To clarify the usage, consider the following example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(&#8216;SQLFUNCTION&#8217;, 4, 3)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This call extracts a substring starting at the 4th character of the string &#8216;SQLFUNCTION&#8217; and continues for 3 characters. The result is &#8216;FUN&#8217;.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If the length parameter is omitted, such as:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(&#8216;SQLFUNCTION&#8217;, 4)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The function returns the substring from the 4th character to the end: &#8216;FUNCTION&#8217;.<\/span><\/p>\n<p><b>Important Considerations When Using SUBSTRING<\/b><\/p>\n<p><b>Handling Negative Start Values<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Using negative values for the start parameter allows you to extract characters relative to the end of the string. For instance, if you want to get the last 3 characters of a string, you can specify a negative start value:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(&#8216;DATABASE&#8217;, -3, 3)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This returns &#8216;ASE&#8217;, the last three characters. This feature is particularly useful when the length of the string varies, but you need consistent characters counted from the end.<\/span><\/p>\n<p><b>Constraints on the Length Parameter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The length parameter must be a positive integer. If you specify a negative length or zero, the SQL engine will return an error. Always ensure that the length parameter is either omitted or a positive number to avoid runtime errors.<\/span><\/p>\n<p><b>What Happens When the Start Position Is Out of Range?<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If the start position exceeds the length of the string, the substring function will return an empty string or a blank space. This means no characters can be extracted beyond the actual string length. It is important to handle such cases in your queries, especially when start values are dynamically calculated.<\/span><\/p>\n<p><b>Length Parameter Greater Than the Remaining String<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If the length specified extends beyond the end of the string, SQL will simply return the substring from the start position to the string\u2019s end. There is no error in this case. For example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(&#8216;DATA&#8217;, 3, 10)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Returns &#8216;TA&#8217;, because the string only has two characters from the 3rd position to the end.<\/span><\/p>\n<p><b>Mandatory Parameters<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The string and start parameters are mandatory. Omitting either of these will result in a syntax error. The length parameter is optional but recommended when you want a substring of a specific length.<\/span><\/p>\n<p><b>Practical Examples of Using SUBSTRING<\/b><\/p>\n<p><b>Extracting Substrings from a Fixed String<\/b><\/p>\n<p><span style=\"font-weight: 400;\">You can use the substring function directly within SQL queries to extract substrings from fixed strings. For example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(&#8216;EXTRACTTHIS&#8217;, 3, 5) AS ExtractedPart;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query returns the substring &#8216;TRACT&#8217; starting from the third character and spanning five characters.<\/span><\/p>\n<p><b>Using SUBSTRING in SELECT Statements<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring is commonly combined with the SELECT statement to retrieve parts of string data stored in tables. For instance, extracting a portion of a user\u2019s email address stored in a column.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(email, 1, 10) AS EmailStart FROM users;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This retrieves the first ten characters from the email field for every user.<\/span><\/p>\n<p><b>Extracting Substrings Including Spaces<\/b><\/p>\n<p><span style=\"font-weight: 400;\">It is important to note that spaces are counted as characters within strings. If your target substring includes spaces, they will be included in the extraction unless explicitly trimmed or removed later.<\/span><\/p>\n<p><b>Extracting Substrings from the End of a String<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To extract characters starting from a position counted from the end of the string, use a negative start value. For example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(&#8216;PROGRAMMING&#8217;, -4, 4) AS LastFourChars;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This extracts &#8216;MING&#8217;, the last four characters of the string.<\/span><\/p>\n<p><b>Introduction to Extracting Substrings from Columns<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The real power of the SQL substring function is revealed when it is applied to actual database tables. Unlike static strings, data stored in tables often varies in length and format, making substring extraction a crucial tool for data analysis, transformation, and reporting. Extracting substrings from columns allows users to isolate meaningful parts of data, such as codes, identifiers, or segments of text, without exporting the entire data set for external processing.<\/span><\/p>\n<p><b>Basic Syntax for Substring Extraction From Columns<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The syntax used to extract substrings from table columns integrates the substring function within a SELECT statement. It follows this structure:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(column_name, start, length)\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM table_name<\/span><\/p>\n<p><span style=\"font-weight: 400;\">WHERE [condition];<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>column_name<\/b><span style=\"font-weight: 400;\"> refers to the column containing the string data.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Start<\/b><span style=\"font-weight: 400;\"> and <\/span><b>length<\/b><span style=\"font-weight: 400;\"> parameters behave as described previously, specifying where to begin and how many characters to extract.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>The WHERE<\/b><span style=\"font-weight: 400;\"> clause is optional and used to filter rows based on specific conditions.<\/span><\/li>\n<\/ul>\n<p><b>Example: Extracting Substrings from a Column<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Consider a table named <\/span><span style=\"font-weight: 400;\">employees<\/span><span style=\"font-weight: 400;\"> with a column <\/span><span style=\"font-weight: 400;\">EmployeeCode<\/span><span style=\"font-weight: 400;\"> containing alphanumeric strings. To extract the first three characters of each employee\u2019s code:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(EmployeeCode, 1, 3) AS CodePrefix<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query returns a list of the first three characters for every employee code in the table. Such extraction might be used to identify department codes or region prefixes.<\/span><\/p>\n<p><b>Applying Conditions to Refine Substring Extraction<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To refine data retrieval, the substring function can be combined with filtering conditions in the WHERE clause. For example, to extract the first letter of employee names only for those earning above a certain salary:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT EmployeeID, SUBSTRING(EmployeeName, 1, 1) AS Initial<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees<\/span><\/p>\n<p><span style=\"font-weight: 400;\">WHERE Salary &gt; 30000;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query returns the employee ID and the first character of their name, but only for employees with salaries greater than 30,000. The WHERE clause limits the result set for targeted analysis.<\/span><\/p>\n<p><b>Extracting Substrings From the End of Column Values<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Extracting characters from the end of column values is possible by using negative start values if supported by your SQL dialect, or by calculating the position from the length of the string. Since not all SQL variants support negative indexing, the length of the string can be used to compute the starting position.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, to extract the last 4 characters of a string in a column,n <\/span><span style=\"font-weight: 400;\">Dept_ID<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(Dept_ID, LEN(Dept_ID) &#8212; 3, 4) AS LastFourDigits<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM departments;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Here, <\/span><span style=\"font-weight: 400;\">LEN(Dept_ID)<\/span><span style=\"font-weight: 400;\"> returns the length of the string, and subtracting 3 sets the start position to the fourth last character. This approach works in SQL Server and similar environments.<\/span><\/p>\n<p><b>Handling Null Values and Empty Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">When working with substring extraction, null values or empty strings in columns require special handling. Applying the substring function to a null value will typically return null. It is important to account for this when writing queries, especially if nulls represent missing or incomplete data.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To avoid errors or unintended results, you can use the <\/span><span style=\"font-weight: 400;\">COALESCE<\/span><span style=\"font-weight: 400;\"> or <\/span><span style=\"font-weight: 400;\">ISNULL<\/span><span style=\"font-weight: 400;\"> functions to replace null values with empty strings before extraction:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(COALESCE(EmployeeName, &#187;), 1, 3) AS NamePrefix<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query ensures that if <\/span><span style=\"font-weight: 400;\">EmployeeName<\/span><span style=\"font-weight: 400;\"> is null, an empty string is used instead, preventing null results.<\/span><\/p>\n<p><b>Advanced Techniques for Substring Extraction<\/b><\/p>\n<p><b>Using Substring With Other String Functions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring is often used in combination with other string functions to achieve more complex manipulations.<\/span><\/p>\n<p><b>Combining with CHARINDEX or INSTR<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In scenarios where the start position of the substring depends on a particular character\u2019s location, functions like <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\"> (SQL Server) or <\/span><span style=\"font-weight: 400;\">INSTR<\/span><span style=\"font-weight: 400;\"> ( MySQL) are invaluable.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, to extract the domain name from an email address stored in a column <\/span><span style=\"font-weight: 400;\">Email<\/span><span style=\"font-weight: 400;\">, the following query locates the &#8216;@&#8217; symbol and extracts the domain:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(Email, CHARINDEX(&#8216;@&#8217;, Email) + 1, LEN(Email)) AS Domain<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM users;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Here, <\/span><span style=\"font-weight: 400;\">CHARINDEX(&#8216;@&#8217;, Email)<\/span><span style=\"font-weight: 400;\"> finds the position of &#8216;@&#8217;, and the substring extracts everything after that position.<\/span><\/p>\n<p><b>Extracting Before or After a Delimiter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Similarly, to extract the username part before the &#8216;@&#8217; in an email:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(Email, 1, CHARINDEX(&#8216;@&#8217;, Email) &#8212; 1) AS Username<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM users;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This extracts the portion of the string before the &#8216;@&#8217; symbol.<\/span><\/p>\n<p><b>Using Substring for Data Cleaning and Standardization<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring extraction can also assist in cleaning or standardizing data formats. For example, phone numbers often include country codes and area codes embedded in longer strings. Extracting only relevant parts makes data easier to analyze.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Suppose a phone number column <\/span><span style=\"font-weight: 400;\">PhoneNumber<\/span><span style=\"font-weight: 400;\"> stores numbers in the format &#8216;+1-202-555-0173&#8217;. To extract the area code &#8216;202&#8217;:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(PhoneNumber, 4, 3) AS AreaCode<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM contacts;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query skips the first three characters &#8216;+1-&#8216; and extracts the next three digits.<\/span><\/p>\n<p><b>Extracting Variable-Length Substrings Using Dynamic Lengths<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In some cases, the length of the substring is not fixed but depends on the location of a delimiter or pattern in the string. Combining a substring with functions like <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\"> enables dynamic substring length determination.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, extracting the first name from a full name stored as &#8216;FirstName LastName&#8217;:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(FullName, 1, CHARINDEX(&#8216; &#8216;, FullName) &#8212; 1) AS FirstName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This extracts characters from the start of <\/span><span style=\"font-weight: 400;\">FullName<\/span><span style=\"font-weight: 400;\"> up to the first space character, capturing the first name regardless of length.<\/span><\/p>\n<p><b>Handling Edge Cases in Substring Extraction<\/b><\/p>\n<p><b>Start Position Beyond String Length<\/b><\/p>\n<p><span style=\"font-weight: 400;\">When the start position is greater than the length of the string in a column, substring returns an empty string. It is important to design queries that handle or prevent this scenario to avoid unexpected blank results.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(EmployeeCode, 10, 3) AS ExtractedPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If <\/span><span style=\"font-weight: 400;\">EmployeeCode<\/span><span style=\"font-weight: 400;\"> has fewer than 10 characters, the result will be blank.<\/span><\/p>\n<p><b>Length Parameter Exceeding String Length<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If the specified length exceeds the remaining string length from the start position, the substring returns all available characters without error.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(EmployeeCode, 5, 10) AS ExtractedPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If only 6 characters remain starting at position 5, all 6 will be returned.<\/span><\/p>\n<p><b>Null and Empty Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">As mentioned earlier, null values in the column result in null outputs from the substring. Empty strings return empty substrings regardless of parameters.<\/span><\/p>\n<p><b>Combining Substring With Other Clauses<\/b><\/p>\n<p><b>Filtering Based on Substring Values<\/b><\/p>\n<p><span style=\"font-weight: 400;\">You can filter query results based on substring values by including substring calls in the WHERE clause.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, to find employees whose department codes start with &#8216;HR&#8217;:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT EmployeeID, Dept_ID<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees<\/span><\/p>\n<p><span style=\"font-weight: 400;\">WHERE SUBSTRING(Dept_ID, 1, 2) = &#8216;HR&#8217;;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This returns employees belonging to HR departments.<\/span><\/p>\n<p><b>Using Substring in ORDER BY<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring can be used to sort data based on specific parts of string columns.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT EmployeeID, EmployeeName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees<\/span><\/p>\n<p><span style=\"font-weight: 400;\">ORDER BY SUBSTRING(EmployeeName, 1, 1);<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This orders employees alphabetically by the first letter of their name.<\/span><\/p>\n<p><b>Practical Case Studies<\/b><\/p>\n<p><b>Case Study: Extracting Product Codes<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In a product inventory table, product codes may be stored as composite strings like &#8216;CAT12345-2024&#8217;, where &#8216;CAT&#8217; is the category, &#8216;12345&#8217; is the item number, and &#8216;2024&#8217; is the year.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To extract just the category prefix:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(ProductCode, 1, 3) AS Category<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM products;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To extract the item number, assuming the category is always 3 characters and a hyphen follows the item number:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(ProductCode, 4, CHARINDEX(&#8216;-&#8216;, ProductCode) &#8212; 4) AS ItemNumber<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM products;<\/span><\/p>\n<p><b>Case Study: Extracting Year from Date Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If dates are stored as strings like &#8216;20240602&#8217; (YYYYMMDD), extracting the year portion is straightforward:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(DateString, 1, 4) AS Year<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM sales;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This query extracts the first four characters, representing the year.<\/span><\/p>\n<p><b>Best Practices for Using Substring in SQL Queries<\/b><\/p>\n<p><b>Always Validate String Lengths<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To avoid errors or blanks, check string lengths before applying a substring. You can use conditional logic, such as CASE statements or WHERE clauses, to handle short strings gracefully.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pgsql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0CASE\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0WHEN LEN(EmployeeCode) &gt;= 3 THEN SUBSTRING(EmployeeCode, 1, 3)\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0ELSE EmployeeCode\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0END AS CodePrefix<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM employees;<\/span><\/p>\n<p><b>Combine Substring With String Functions for Flexibility<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Use a substring in combination with other functions like TRIM, REPLACE, or CONCAT to clean and manipulate extracted substrings further.<\/span><\/p>\n<p><b>Use Aliases for Readability<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Always alias your substring outputs in SELECT statements for better readability and maintainability of SQL code.<\/span><\/p>\n<p><b>Test Queries with Sample Data<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Before running substring queries on large datasets, test them on sample data to ensure parameters and results meet expectations.<\/span><\/p>\n<p><b>Introduction to Advanced Substring Techniques<\/b><\/p>\n<p><span style=\"font-weight: 400;\">While the substring function is straightforward in its basic use\u2014extracting a portion of a string\u2014it can be leveraged in much more complex scenarios. As databases grow larger and queries become more intricate, substring operations may be combined with other SQL features, applied conditionally, or optimized for performance.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This section explores advanced techniques, including nested substring operations, performance tuning, cross-platform syntax differences, and common pitfalls.<\/span><\/p>\n<p><b>Nested Substring Operations<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Nested substring operations occur when one substring function is placed inside another, allowing you to extract a substring of a substring. This technique is useful when working with multi-part strings that require stepwise parsing.<\/span><\/p>\n<p><b>Example: Extracting Middle Sections From Complex Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Suppose you have a string representing a file path or URL, such as:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">makefile<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">C:\\Users\\JohnDoe\\Documents\\Report2024.pdf<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If you want to extract just the file name &#171;Report2024.pdf&#187;, but the directory depth varies, one approach is:<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use a substring to find the last backslash position.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Extract the substring after that position.<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400;\">In SQL Server, you might combine <\/span><span style=\"font-weight: 400;\">RIGHT<\/span><span style=\"font-weight: 400;\">, <\/span><span style=\"font-weight: 400;\">LEN<\/span><span style=\"font-weight: 400;\">, and <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT RIGHT(FilePath, LEN(FilePath) &#8212; CHARINDEX(&#8216;\\&#8217;, REVERSE(FilePath)) + 1) AS FileName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Documents;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This reverses the string, finds the first backslash, and extracts the file name accordingly.<\/span><\/p>\n<p><b>Nested Substring to Extract Portions Within Portions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Alternatively, a nested substring can be used like this:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0SUBSTRING(FullString, start1, length1),<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0start2, length2<\/span><\/p>\n<p><span style=\"font-weight: 400;\">) AS NestedSubstring<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM table;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This extracts a substring from a string. For example, if you first extract the middle portion of a string and then want a specific section within that middle portion.<\/span><\/p>\n<p><b>Practical Scenario: Parsing Product Serial Numbers<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Consider product serial numbers in the format:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">AA-1234-BB-5678-CC<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To extract the numeric part between the first and second hyphens:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0SUBSTRING(SerialNumber, CHARINDEX(&#8216;-&#8216;, SerialNumber) + 1, LEN(SerialNumber)),<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a01,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CHARINDEX(&#8216;-&#8216;, SUBSTRING(SerialNumber, CHARINDEX(&#8216;-&#8216;, SerialNumber) + 1, LEN(SerialNumber))) &#8212; 1<\/span><\/p>\n<p><span style=\"font-weight: 400;\">) AS NumericPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Products;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Here, the inner substring extracts the string after the first hyphen, and the outer substring extracts the numeric part before the next hyphen.<\/span><\/p>\n<p><b>Conditional Substring Extraction<\/b><\/p>\n<p><b>Using CASE with Substring<\/b><\/p>\n<p><span style=\"font-weight: 400;\">SQL\u2019s <\/span><span style=\"font-weight: 400;\">CASE<\/span><span style=\"font-weight: 400;\"> statement enables conditional logic, allowing substring extraction to vary based on data.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, if you want to extract the first 3 characters if a code starts with &#8216;A&#8217;, and the first 5 characters otherwise:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CASE\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN LEFT(Code, 1) = &#8216;A&#8217; THEN SUBSTRING(Code, 1, 3)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE SUBSTRING(Code, 1, 5)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0END AS ExtractedPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Items;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This logic tailors substring extraction per row, enhancing flexibility.<\/span><\/p>\n<p><b>Handling Variable-Length Strings Conditionally<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If some strings are shorter than the desired substring length, you can adjust extraction with CASE to avoid errors or blanks:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CASE\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN LEN(ColumnName) &gt;= 5 THEN SUBSTRING(ColumnName, 1, 5)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE ColumnName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0END AS SafeSubstring<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Performance Considerations When Using Substring<\/b><\/p>\n<p><b>Impact on Query Performance<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring operations, especially when applied to large tables and in WHERE clauses, can slow down queries since they require scanning and processing of string data on each row.<\/span><\/p>\n<p><b>Avoid Using Substring on Indexed Columns in WHERE Clauses<\/b><\/p>\n<p><span style=\"font-weight: 400;\">When filtering data with a WHERE clause that applies a substring on an indexed column, the database may be forced to perform a full table scan because indexes on raw columns cannot typically be used when functions transform the data.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For example, this query may not use the index:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT * FROM employees WHERE SUBSTRING(EmployeeCode, 1, 3) = &#8216;HR1&#8217;;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Instead, if possible, consider storing extracted parts in separate columns indexed for efficient querying.<\/span><\/p>\n<p><b>Using Computed Columns and Indexes<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Some databases support computed or generated columns that store substring values. Indexing these columns allows faster filtering and sorting on substrings without computing them at query time.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example in SQL Server:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">ALTER TABLE employees ADD CodePrefix AS SUBSTRING(EmployeeCode, 1, 3) PERSISTED;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CREATE INDEX idx_CodePrefix ON employees(CodePrefix);<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Queries on <\/span><span style=\"font-weight: 400;\">CodePrefix<\/span><span style=\"font-weight: 400;\"> can now efficiently use indexes.<\/span><\/p>\n<p><b>Avoid Overusing Nested Substrings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Nested substrings increase computation complexity. Where possible, extract necessary substrings once in subqueries or CTEs (Common Table Expressions) rather than repeatedly in large queries.<\/span><\/p>\n<p><b>Cross-Database Differences in Substring Functions<\/b><\/p>\n<p><b>Overview<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Although <\/span><span style=\"font-weight: 400;\">SUBSTRING()<\/span><span style=\"font-weight: 400;\"> is supported across most SQL dialects, subtle differences exist in syntax, function names, and behavior.<\/span><\/p>\n<p><b>SQL Server<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Syntax:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(expression, start, length)<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Start<\/span><span style=\"font-weight: 400;\"> can be positive or negative.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">If <\/span><span style=\"font-weight: 400;\">start<\/span><span style=\"font-weight: 400;\"> is beyond the string length, returns an empty string.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LEN()<\/span><span style=\"font-weight: 400;\"> returns the string length.<\/span><\/li>\n<\/ul>\n<p><b>MySQL<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Syntax:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(str, pos, len)<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Pos<\/span><span style=\"font-weight: 400;\"> can be positive (from start) or negative (from end).<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Negative <\/span><span style=\"font-weight: 400;\">pos<\/span><span style=\"font-weight: 400;\"> counts from the end of the string.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LENGTH()<\/span><span style=\"font-weight: 400;\"> returns string length in bytes, and <\/span><span style=\"font-weight: 400;\">CHAR_LENGTH()<\/span><span style=\"font-weight: 400;\"> returns length in characters.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(&#8216;Hello World&#8217;, -5, 3); &#8212; returns &#8216;Wor&#8217;<\/span><\/p>\n<p><b>PostgreSQL<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Syntax:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(string FROM start FOR count)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Or the standard form:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SUBSTRING(string, start, count)<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Supports negative indices differently.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use <\/span><span style=\"font-weight: 400;\">LENGTH()<\/span><span style=\"font-weight: 400;\"> for length.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(&#8216;Hello World&#8217; FROM 7 FOR 5); &#8212; returns &#8216;World&#8217;<\/span><\/p>\n<p><b>Practical Tips for Using Substring Effectively<\/b><\/p>\n<p><b>Validate Inputs<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Always ensure that inputs for start and length are within expected ranges to avoid runtime errors or unexpected blanks.<\/span><\/p>\n<p><b>Use Descriptive Aliases<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Alias the output columns clearly, especially in complex queries involving multiple substring operations.<\/span><\/p>\n<p><b>Document Complex Expressions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Comment your SQL queries when nested substrings or dynamic computations are involved to improve maintainability.<\/span><\/p>\n<p><b>Test on Edge Cases<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Test queries on rows with short strings, null values, and unexpected formats to ensure robust behavior.<\/span><\/p>\n<p><b>Optimize with Temporary Tables or CTEs<\/b><\/p>\n<p><span style=\"font-weight: 400;\">For complex substring extractions repeated multiple times, compute them once in a CTE or temporary table.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">WITH ExtractedParts AS (<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0SELECT<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ID,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUBSTRING(ColumnName, 1, 5) AS Part1,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUBSTRING(ColumnName, 6, 3) AS Part2<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0FROM TableName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT * FROM ExtractedParts WHERE Part1 = &#8216;ABCDE&#8217;;<\/span><\/p>\n<p><b>Using Substring in Data Transformation and ETL Processes<\/b><\/p>\n<p><b>Extracting Data During Imports<\/b><\/p>\n<p><span style=\"font-weight: 400;\">When importing data from flat files or external sources, substring extraction can parse and transform raw strings into meaningful fields.<\/span><\/p>\n<p><b>Example: Parsing Fixed-Width Files<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In fixed-width files, fields have predetermined positions and lengths. Using a substring in SQL during import can extract each field correctly.<\/span><\/p>\n<p><b>Example: Extracting Codes From Combined Fields<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Some datasets combine multiple data points in one column. Substring helps normalize this data into separate columns during ETL.<\/span><\/p>\n<p><b>Substring in Data Validation and Quality Checks<\/b><\/p>\n<p><b>Verifying Code Formats<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Use a substring to check if codes begin or end with expected prefixes or suffixes.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT *<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM products<\/span><\/p>\n<p><span style=\"font-weight: 400;\">WHERE SUBSTRING(ProductCode, 1, 3) &lt;&gt; &#8216;CAT&#8217;;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This identifies products with codes not starting with &#8216;CAT&#8217;.<\/span><\/p>\n<p><b>Checking String Lengths and Patterns<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Combine a substring with length and pattern matching functions (like LIKE or REGEXP) for comprehensive validation.<\/span><\/p>\n<p><b>Substring in Reporting and Presentation<\/b><\/p>\n<p><b>Displaying Partial Data<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Extract and display only relevant parts of data fields in reports, such as masked IDs or initials.<\/span><\/p>\n<p><b>Creating Custom Sorting Keys<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Generate sort keys from substrings of textual data for customized report ordering.<\/span><\/p>\n<p><b>Troubleshooting Common Issues With Substring<\/b><\/p>\n<p><b>Empty Results or Nulls<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Confirm the data isn\u2019t null and start positions are within the string length to avoid empty or null results.<\/span><\/p>\n<p><b>Off-By-One Errors<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Be careful with start positions and length calculations, especially when combined with character position functions.<\/span><\/p>\n<p><b>Unexpected Behavior Across Databases<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Test substring queries on your specific database system to account for dialect differences.<\/span><\/p>\n<p><b>Combining Substring with CHARINDEX \/ INSTR for Dynamic Positioning<\/b><\/p>\n<p><span style=\"font-weight: 400;\">One common use case is extracting substrings where the start position is not fixed but depends on the position of a delimiter or character.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In SQL Server, <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\"> is used:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(ColumnName, CHARINDEX(&#8216;-&#8216;, ColumnName) + 1, 5) AS ExtractedPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Here, the substring starts immediately after the first hyphen.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In MySQL , the equivalent is <\/span><span style=\"font-weight: 400;\">INSTR<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTR(ColumnName, INSTR(ColumnName, &#8216;-&#8216;) + 1, 5) AS ExtractedPart<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Using SUBSTRING with CONCAT for Reformatting Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">You can extract substrings and concatenate them with other strings or substrings to reformat data fields.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Reformatting phone numbers from <\/span><span style=\"font-weight: 400;\">1234567890<\/span><span style=\"font-weight: 400;\"> to <\/span><span style=\"font-weight: 400;\">(123) 456-7890<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CONCAT(<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8216;(&#8216;, SUBSTRING(PhoneNumber, 1, 3), &#8216;) &#8216;,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUBSTRING(PhoneNumber, 4, 3), &#8216;-&#8216;,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUBSTRING(PhoneNumber, 7, 4)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0) AS FormattedPhone<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Contacts;<\/span><\/p>\n<p><b>Employing SUBSTRING with REPLACE for Cleanup<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In scenarios where unwanted characters appear in strings, <\/span><span style=\"font-weight: 400;\">REPLACE<\/span><span style=\"font-weight: 400;\"> can be used before or after substring extraction.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Remove spaces before extracting:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(REPLACE(ColumnName, &#8216; &#8216;, &#187;), 1, 5) AS CleanedSubstring<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Complex String Manipulations Using Substring<\/b><\/p>\n<p><b>Parsing Multi-Delimited Strings<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Some data fields contain multiple delimiters (e.g., commas, semicolons). Extracting specific parts may require multiple substring and delimiter position computations.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Extract the second item from a comma-separated list:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0SUBSTRING(<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColumnName,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX(&#8216;,&#8217;, ColumnName) + 1,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX(&#8216;,&#8217;, ColumnName, CHARINDEX(&#8216;,&#8217;, ColumnName) + 1) &#8212; CHARINDEX(&#8216;,&#8217;, ColumnName) &#8212; 1<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0) AS SecondItem<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Explanation:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Find the first comma position.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Find the second comma position after the first.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Extract the substring between the two commas.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">This approach can be extended for the nth item extraction by nesting or looping in procedural SQL.<\/span><\/p>\n<p><b>Extracting File Extensions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To get file extensions from filenames like <\/span><span style=\"font-weight: 400;\">report_final.pdf<\/span><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(Filename, LEN(Filename) &#8212; CHARINDEX(&#8216;.&#8217;, REVERSE(Filename)) + 2, LEN(Filename)) AS Extension<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Files;<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Reverse the string.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Find the position of the first dot.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Calculate the start position for extension.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Extract a substring.<\/span><\/li>\n<\/ul>\n<p><b>Handling Nulls and Edge Cases<\/b><\/p>\n<p><b>Dealing with Null Values<\/b><\/p>\n<p><span style=\"font-weight: 400;\">If the string is <\/span><span style=\"font-weight: 400;\">NULL<\/span><span style=\"font-weight: 400;\">, substring functions typically return <\/span><span style=\"font-weight: 400;\">NULL<\/span><span style=\"font-weight: 400;\">. This behavior should be accounted for in queries.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Use <\/span><span style=\"font-weight: 400;\">COALESCE<\/span><span style=\"font-weight: 400;\"> to default nulls to empty strings:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(COALESCE(ColumnName, &#187;), 1, 5) AS SubstringSafe<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Handling Strings Shorter Than Desired Length<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To avoid errors or unexpected truncation, use <\/span><span style=\"font-weight: 400;\">CASE<\/span><span style=\"font-weight: 400;\"> or <\/span><span style=\"font-weight: 400;\">IF<\/span><span style=\"font-weight: 400;\"> conditions:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CASE<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN LEN(ColumnName) &lt; 5 THEN ColumnName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE SUBSTRING(ColumnName, 1, 5)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0END AS SafeSubstring<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Protecting Against Invalid Start Positions<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Start positions less than 1 should be adjusted or defaulted to 1.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT SUBSTRING(ColumnName, CASE WHEN start_pos &lt; 1 THEN 1 ELSE start_pos END, 5)<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Using Substring in Data Cleaning and Standardization<\/b><\/p>\n<p><b>Extracting and Normalizing Codes<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In datasets with inconsistent code formatting, a substring helps isolate meaningful parts for standardization.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Extract the first three characters as category codes:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">UPDATE Products<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SET CategoryCode = UPPER(SUBSTRING(ProductCode, 1, 3));<\/span><\/p>\n<p><b>Removing Prefixes or Suffixes<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To strip unwanted prefixes or suffixes before further processing:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0CASE<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN LEFT(ColumnName, 3) = &#8216;PRE&#8217; THEN SUBSTRING(ColumnName, 4, LEN(ColumnName))<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE ColumnName<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0END AS CleanedString<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM TableName;<\/span><\/p>\n<p><b>Substring in Data Security and Privacy<\/b><\/p>\n<p><b>Masking Sensitive Information<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring is useful for masking parts of sensitive data, such as credit card numbers.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Example: Show only the last four digits:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sql<\/span><\/p>\n<p><span style=\"font-weight: 400;\">CopyEdit<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SELECT CONCAT(&#8216;**** **** **** &#8216;, SUBSTRING(CardNumber, LEN(CardNumber) &#8212; 3, 4)) AS MaskedCard<\/span><\/p>\n<p><span style=\"font-weight: 400;\">FROM Payments;<\/span><\/p>\n<p><b>Troubleshooting and Debugging Substring Queries<\/b><\/p>\n<p><b>Common Errors and Their Causes<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Syntax Errors<\/b><span style=\"font-weight: 400;\">: Usually due to missing parameters or incorrect function names.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Empty or Null Returns<\/b><span style=\"font-weight: 400;\">: Often caused by invalid start positions or null inputs.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Incorrect Results<\/b><span style=\"font-weight: 400;\">: Off-by-one mistakes in start or length parameters.<\/span><\/li>\n<\/ul>\n<p><b>Debugging Tips<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Test with fixed string literals first.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use length and position functions (<\/span><span style=\"font-weight: 400;\">LEN<\/span><span style=\"font-weight: 400;\">, <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\">) to verify input data.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use CASE statements to handle exceptions.<\/span><\/li>\n<\/ul>\n<p><b>Best Practices for Maintaining Substring Usage in Large Systems<\/b><\/p>\n<p><b>Documentation and Code Comments<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Document complex substring logic clearly to aid future maintenance.<\/span><\/p>\n<p><b>Use Views or CTEs for Reusable Logic<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Encapsulate substring computations in database views or CTEs for clarity and reuse.<\/span><\/p>\n<p><b>Optimize for Performance<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Avoid substrings in WHERE clauses on large datasets unless indexed computed columns are available.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Cache results if computations are heavy and repeated.<\/span><\/li>\n<\/ul>\n<p><b>Final Thoughts<\/b><\/p>\n<p><b>The Versatility of the SQL Substring Function<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The SQL substring function serves as a core utility for handling string-based data within relational databases. Its value lies not only in extracting character segments but also in enabling dynamic, context-sensitive transformations that adapt to diverse data patterns. Whether working on structured enterprise systems or less predictable user-generated content, the substring function consistently provides a precise and efficient solution.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It can be used in both analytical and transactional queries, making it highly versatile across disciplines like business intelligence, data warehousing, reporting, and application development. From slicing out identifiers in compound keys to transforming display formats for UI consumption, the applications of the substring function span across technical and business domains.<\/span><\/p>\n<p><b>Enhancing Functionality Through Integration<\/b><\/p>\n<p><span style=\"font-weight: 400;\">One of the most powerful features of the SQL substring function is its ability to integrate seamlessly with other functions. When used alongside pattern-matching functions like <\/span><span style=\"font-weight: 400;\">CHARINDEX<\/span><span style=\"font-weight: 400;\"> or <\/span><span style=\"font-weight: 400;\">INSTR<\/span><span style=\"font-weight: 400;\">, or string-cleanup functions like <\/span><span style=\"font-weight: 400;\">REPLACE<\/span><span style=\"font-weight: 400;\">, it becomes possible to construct highly adaptable queries. These combinations allow users to parse strings of unknown formats, identify specific patterns, and isolate useful components, all while staying within SQL.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This integration capability is critical when dealing with unstructured or semi-structured text, such as log entries, composite codes, or metadata fields. Substring can help extract values like status codes, usernames, timestamps, or file extensions that would otherwise require external parsing logic.<\/span><\/p>\n<p><b>String Manipulation for Data Quality and Standardization<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Substring also plays a vital role in data quality initiatives. It can enforce formatting rules by truncating overly long inputs or isolating the most relevant part of a string. Organizations frequently deal with dirty data\u2014values that have inconsistent formatting, unwanted characters, or embedded delimiters. Substring allows engineers to build routines that systematically extract and normalize such values, leading to cleaner, more reliable datasets.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In standardization workflows, a substring helps convert variable-length inputs into consistent formats. For instance, extracting the last four digits of a ZIP code, pulling the domain from email addresses, or truncating descriptions for display purposes all benefit from substring logic.<\/span><\/p>\n<p><b>Substring for Data Privacy and Masking<\/b><\/p>\n<p><span style=\"font-weight: 400;\">As regulatory environments tighten, data security becomes increasingly important. Substring functions can assist in data privacy by masking parts of personally identifiable information (PII). In a system that stores full social security numbers, using a substring to display only the last four digits while masking the rest ensures compliance with data protection policies.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Masking is especially important in data lakes, dashboards, and development environments where full access to sensitive data should be restricted. Substring provides a lightweight, in-database solution that doesn\u2019t require additional encryption or application-layer filtering.<\/span><\/p>\n<p><b>Avoiding Pitfalls with Best Practices<\/b><\/p>\n<p><span style=\"font-weight: 400;\">To avoid common pitfalls, substring logic should be implemented with care. Always validate the length and existence of the source string to prevent unexpected nulls or errors. Use conditional statements like <\/span><span style=\"font-weight: 400;\">CASE<\/span><span style=\"font-weight: 400;\"> or <\/span><span style=\"font-weight: 400;\">COALESCE<\/span><span style=\"font-weight: 400;\"> to ensure fallback behaviors. When possible, avoid using substrings in filtering conditions unless the performance has been tested on real data sizes.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For maintainability, substring expressions should be abstracted into views, stored procedures, or common table expressions (CTEs), especially when reused across different reports or modules. This not only promotes reusability but also reduces duplication and the risk of inconsistency.<\/span><\/p>\n<p><b>Closing Perspective<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Ultimately, substring is more than just a function, it\u2019s a gateway into text analytics within SQL. It empowers users to reshape, clean, protect, and understand their data. While it may appear to be a simple utility, its true potential is realized when paired with thoughtful query design and strategic implementation. In a world increasingly driven by text-rich data, mastering the substring function is a necessary step toward becoming a proficient and agile data professional.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every data professional approaches the task of retrieving information differently, depending on the specific needs of their analysis or application. SQL, or Structured Query Language, offers a range of tools that help users extract and manipulate data efficiently. One of the fundamental functions available in SQL for handling strings is the substring function. This function provides a powerful way to extract a specific set of characters from a larger string, allowing for precise data manipulation and retrieval. What Is the Substring Function in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1018,1027],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/posts\/817"}],"collection":[{"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/comments?post=817"}],"version-history":[{"count":4,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/posts\/817\/revisions"}],"predecessor-version":[{"id":9175,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/posts\/817\/revisions\/9175"}],"wp:attachment":[{"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/media?parent=817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/categories?post=817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.certbolt.com\/certification\/wp-json\/wp\/v2\/tags?post=817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}