Now let’s throw a twist into the mix so we can see how changing the start position to a negative number gives us a begin point at the end of our source data. Here are the recordset of results Postgres returns to us, giving us characters 1 through 8 of the t_category field (column), results renamed to be “t_category_type” by the “AS” operator you see above: id_tech , SUBSTRING (t_category, 1, 8 ) AS t_category_type The new query that gives us the information we need:
We want to keep this as simple and readable as possible. Side note: If you are familiar with normalization, you might look at the above table and think to yourself, “I’d definitely normalize this table by creating a new table called ‘categories’, and link it to this table via a new field in this table called id_category. So we’ll take baby steps and modify that query: The initial thing you may have noticed about our first example that returns only one column of data, either “Database” or “Language” and that data is useless to us if not combined with at least an id or name of what tech is being referred to.
#Pervasive postgres full#
Why use the SUBSTRING function? Use case of categorizing database and coding technologiesĪgain, here is our full table. Now that we have a basic understanding of how the SUBSTRING function is used, we can get even more “real world” with our training. The above query will return the following results: t_category_type SUBSTRING (t_category, 1, 8 ) AS t_category_type Also, we want the first word and we can’t assume we know what that word is, so this is where the LIKE and “%” method breaks down. What if we want to return the first word in the t category field (column) of our database? We _could use the LIKE and “%” operators but we’re here to learn SUBSTRING. Our test table with already filled-in data: id_tech
Here, we want to mine our database for the first word in a value stored in a column that may have multiple words. Let’s look at a slightly deeper example in SQL of how SUBSTRING works. The example above returns “Python” which is a string of the first 1 thru 6 characters of our original string. T_string_portion = SUBSTRING (t_string_original, 1, 6 ) T_string_original : = 'Python is the best coding language.' So if you seed the function with something like, “PostgreSQL rocks,” and tell it you want the first 10 characters of that string, the SUBSTRING() function will return “PostgreSQL”. The PostgreSQL SUBSTRING function returns a portion of a string.
#Pervasive postgres how to#
In this article, we will learn to use the Postgres Substring and Position functions SQL when manipulating strings to isolate words in multi-word strings, including:
0 kommentar(er)
