{"id":10021,"date":"2023-05-04T16:13:08","date_gmt":"2023-05-04T21:13:08","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=10021"},"modified":"2023-05-04T16:13:08","modified_gmt":"2023-05-04T21:13:08","slug":"postgresql-split_part-and-translate","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=10021","title":{"rendered":"Postgresql SPLIT_PART and TRANSLATE"},"content":{"rendered":"<p>We have a database where there&#8217;s a single field, args, into which the vendor has glommed quite a few different things. Unfortunately, I need one of those numbers.<\/p>\n<pre>\"---\r\n- Workbook\r\n- 4477\r\n- Sample Report\r\n- 18116\r\n- null\r\n\"<\/pre>\n<p>You can use split_part to break a column into elements and only use one of those elements <tt>split_part(column_to_split, delimiter, ColumnToKeep)<\/tt><\/p>\n<p>As an example:<br \/>\n<tt>SPLIT_PART(b.args, E'\\n', 3)AS task_workbook_id<\/tt><\/p>\n<p>In this case, I subsequently needed to eliminate the dash and space that prefixed the line. Using TRANSLATE, I am removing the &#8216;- &#8216; with &#8221;:<br \/>\n<tt>TRANSLATE ( SPLIT_PART(b.args, E'\\n', 3), '- ','') AS task_workbook_id<\/tt><\/p>\n<p>And now I&#8217;ve just got 4477<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We have a database where there&#8217;s a single field, args, into which the vendor has glommed quite a few different things. Unfortunately, I need one of those numbers. &#8220;&#8212; &#8211; Workbook &#8211; 4477 &#8211; Sample Report &#8211; 18116 &#8211; null &#8221; You can use split_part to break a column into elements and only use one &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1522],"tags":[1349,922],"class_list":["post-10021","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10021","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10021"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10021\/revisions"}],"predecessor-version":[{"id":10022,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10021\/revisions\/10022"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10021"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10021"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10021"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}