cary huang: Explore Practical PostgreSQL Substring Use Cases with Examples

If you work with strings a lot, you most likely will use substring function quite frequently for your data manipulation tasks. PostgreSQL provides a very versatile substring function that can handle a lot of string manipulation scenarios. In this blog, we’ll dive into real examples to show you how to make the most of substrings in PostgreSQL 16. PostgreSQL Substr With Index and Offset Values Index (one-based) and offset are the simplest ways to extract a substring. Useful for strings with fixed lengths. PostgreSQL provides several methods to specify index bounds. Set Index Bounds as Arguments Start index and offset values can be provided to substring function as argument 2 and 3 where offset is an optional argument. For example: Returns the substring starting at index 1 to 4: SELECT substring('techbuddies', 1, 4); substring ----------- tech (1 row) Returns the substring from index 5 to the end: SELECT substring('techbuddies', 5); substring ----------- buddies (1 row) Set Index Bounds with FROM and FOR Clauses The index bounds can also be set using FROM and FOR clauses; Both are optional of each other, meaning that you can specify FROM without FOR or FOR without FROM. Otherwise they work the same way as arguments. It is also possible to use LENGTH() operator to return the length of the input string, which is handy if you need to compute an index from the end of the string. For example: Extracts the substring starting at index 1 to 4. SELECT substring('techbuddies' FROM 1 FOR 4); substring ----------- tech (1 row) Returns the first 5 characters as substring: SELECT substring('techbuddies.io' FOR 5); substring ----------- techb (1 row) Extracts the last 7 characters from the string and grabs the first 4 as substring SELECT substring('techbuddies.io' FROM LENGTH('techbuddies.io') - 6 for 4); substring ----------- dies (1 row) PostgreSQL Substr With Regular Expression[...]