In my day job (Pipeline Survey Data Analyst) I sometimes have the opportunity to write custom SQL queries in an MS Access database, which is the back end of one of the proprietary GIS solutions, my colleagues use. Although I feel that Access’s SQL implementation is not quite as robust as other SQL dialects, it does get the job done in certain situations (the visual interface continues to grow on me). For a learning experiment, I decided to reproduce – and solve – the same requirements using PostgreSQL, as that I had in the MS Access environment. However, I discovered an all-together different challenge.
Both MS Access and MySQL provide several string functions. One of those is a particularly useful string comparing function. MySQL has STRCMP() while in Access, there is a similar StrComp(). Postgres does not have its own version that I am aware of. I leaned heavily on this type of function in MS Access. Hopefully, readers will clue me in on what Postgres-specific string function I could use in its stead. Meanwhile, read on to see the workaround I used…
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:- Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
- PostgreSQL 11.5
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Postgres, MySQL, and MS Access all provide left() and right() string functions (I’ve written about them before in this post), which returns a specific character or portion of characters from the respective end of the string, depending on which function is called along with the INTEGER value for the number of characters. However, as mentioned in the opener, Postgres does not implement a STRCMP() function (to my knowledge). Having used StrComp() in the solution for MS Access, I sought out other means within Postgres for similar functionality.
For a quick overview, StrComp() accepts 2 string parameters that are compared to one another, along with an optional compare parameter. Depending on the comparison result, a value of either 0, 1, -1 or NULL is returned. A value of 0 indicates the strings are equal, whereas a -1 means ‘string_1’ is less than ‘string_2’. On the other hand, a positive 1 signifies ‘string_1’ is greater than ‘string_2’. Should either ‘string_1’ or ‘string_2’ be NULL, it (NULL) is returned. Feel free to visit the MySQL STRCMP() documentation and the MS Access StrComp() documentation for in-depth information on each respective system’s implementation if you are interested.
Suppose I have a ‘sheet’ table with a ‘sheet_name’ TEXT column:
1 2 3 4 5 | learning=> \d sheet; Table "public.sheet" Column | Type | Collation | Nullable | Default ------------+------+-----------+----------+--------- sheet_name | text | | | |
These values are present:
1 2 3 4 5 6 7 8 | learning=> TABLE sheet; sheet_name ------------ 001 010 007 033 (4 rows) |
At some point, the ‘sheet_name’ column values are used in a page count call-out (E.g., page 1 of 5, page 7 of 7, page 33 of 50, etc…). However, instead of having to manually strip the leading zero(s) from the text after placement in the designated area in AutoCADD on each sheet, it would be ideal to apply this change for all rows in the table once and for all.
I’ll run a few ad-hoc queries utilizing left() and right() for a better visual representation of the comparison results:
1 2 3 4 5 6 7 8 | learning=> SELECT right(sheet_name, 2) FROM sheet; right ------- 01 10 07 33 (4 rows) |
I’ll add in a CASE expression with a brief message indicating the boolean results for each right(sheet_name,2) = '0' comparison:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT sheet_name, CASE WHEN right(sheet_name,2) = '0' THEN 'That is zero' ELSE 'Not zero' END FROM sheet; sheet_name | case ------------+---------- 001 | Not zero 010 | Not zero 007 | Not zero 033 | Not zero (4 rows) |
Wait a minute. Some of those characters are ‘0’. Indeed they are. Yet, most of them have additional characters, which is why the comparison to ‘0’ returns false. Using StrComp(sheet_name, '0') here would return a 1 since ‘string_1’ is greater than ‘0’.
This is where left plays an important role. See the below query for better understanding:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT sheet_name, left(right(sheet_name,2),1) AS leftmost_char, CASE WHEN left(right(sheet_name,2),1) = '0' THEN 'That is zero' ELSE 'Not zero' END FROM sheet; sheet_name | leftmost_char | case ------------+---------------+-------------- 001 | 0 | That is zero 010 | 1 | Not zero 007 | 0 | That is zero 033 | 3 | Not zero (4 rows) |
This query provides a better understanding of why the equality test results were false in the first query.
Determining the leftmost character value is critical for the UPDATE I need, setting these columns to either a single or double-digit appearance accordingly, which would then be reflected on the printed sheets. (Bear in mind they are actually TEXT values)
Using a similar CASE expression with left() and right() as that above, I can UPDATE table ‘sheet’ to reflect the desired changes:
1 2 3 4 5 6 7 | UPDATE sheet SET sheet_name = CASE WHEN left(right(sheet_name,2),1) = '0' THEN right(sheet_name, 1) ELSE right(sheet_name,2) END; UPDATE 4 |
A quick query via the TABLE command shows the UPDATE is successful:
1 2 3 4 5 6 7 8 | learning=> TABLE sheet; sheet_name ------------ 1 10 7 33 (4 rows) |
Admittedly, passing in right() as a parameter of the left() function looks somewhat confusing, yet the logic is relatively straight-forward. If the left-most character value – out of the 2 returned from the right() function call – is equal to ‘0’, that needs to reflect a single-digit representation. Otherwise, the row value should be set to represent a double-digit representation because that value is something other than ‘0’ (i.e., ‘1’ through ‘9’).
It helps tremendously that I am really only concerned with checking a single characters’ value against the ‘0’ character. This solution likely would not work in a different scenario. Especially if testing for length equality – which is where I could see the StrComp() function put to good use – based off of the individual values returned to the caller.
Any feedback and suggestions on what string function – if any – PostgreSQL has that mimics the functionality of either STRCMP() or StrComp() would be awesome to see in the comments below so hit me up with them! Thanks for reading!!!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.