- SUBSTRING – ANSI SQL-2011 compliant function<\/li>
- SUBSTR – Teradata extended ANSI SQL-2011 compliant function<\/li><\/ol>\n\n\n\n
Supported data types<\/h2>\n\n\n\n
Both these functions support Character, Byte, and Numeric data types. There will be an implicit conversion into characters for the Numeric data types. Let us see each of these Teradata substring functions with examples. <\/p>\n\n\n\n
Syntax<\/h2>\n\n\n\n
SUBSTRING(String expression, starting position, number of characters)\nSUBSTR(String expression, starting position, number of characters)<\/pre>\n\n\n\n
Example 1 – Substring using the SUBSTRING function<\/h2>\n\n\n\n
Let us see an example to understand this function better. For example, there is a column in a table that holds the license number of people in a specific format. This could be because the source system has stored the information in that manner. During data migration, if we want to extract and keep them in separate columns, the substring function would be very useful.<\/p>\n\n\n\n
Consider that the license number is stored in a column of a table in the database as shown below<\/p>\n\n\n\n
INDIA-TN-2020-ABC12345(COUNTRY-STATE-LICENSED)<\/p>\n\n\n\n
In this, if you want to extract the state information, you can use the substring function<\/p>\n\n\n\n
SELECT SUBSTRING('INDIA-TN-ABC12345', 7,2);<\/pre>\n\n\n\nOutput<\/h3>\n\n\n\n
![\"\"](\"https:\/\/techieshouts.com\/wp-content\/uploads\/2021\/10\/image-2.png\")
<\/figure>\n\n\n\nExample 2 – Substring using the SUBSTR function<\/h2>\n\n\n\n
Suppose, you are storing the name of the log file in a column of a table for auditing purposes like below. In that, if you want to extract the DATE value alone, you can use the SUBSTR function to extract that.<\/p>\n\n\n\n
SELECT SUBSTRING('logfile_20201002_database.log',9,8) as LogDate;<\/pre>\n\n\n\nOutput<\/h3>\n\n\n\n
![\"\"](\"https:\/\/techieshouts.com\/wp-content\/uploads\/2021\/10\/image-4-1024x368.png\")
<\/figure>\n\n\n\nAlso read,<\/p>\n\n\n\n
- Concatenation function in Teradata<\/a><\/li>
- Fetch logged in user details<\/a><\/li>
- Get the Current date and time in Teradata<\/a><\/li><\/ol>\n","protected":false},"excerpt":{"rendered":"
Be it a programming language or the querying language, the substring function is one of the most commonly used ones. The need of extracting the particular portion in a string is much needed while working with the data especially when the contents are of the same length. This will help a programmer to extract a\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[179,177,208,209,178],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1012"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=1012"}],"version-history":[{"count":5,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1012\/revisions"}],"predecessor-version":[{"id":1215,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1012\/revisions\/1215"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=1012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=1012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=1012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Fetch logged in user details<\/a><\/li>
- Concatenation function in Teradata<\/a><\/li>