{"id":1027,"date":"2021-10-06T00:23:58","date_gmt":"2021-10-05T18:53:58","guid":{"rendered":"https:\/\/techieshouts.com\/home\/?p=1027"},"modified":"2022-08-09T19:02:43","modified_gmt":"2022-08-09T13:32:43","slug":"instr-function-in-teradata","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/instr-function-in-teradata\/","title":{"rendered":"INSTR function in Teradata"},"content":{"rendered":"\n

Teradata<\/a> has multiple functions to work with strings. INSTR function is a very handy one when it comes to search a string inside another. In the other blog, we saw how this is done using the INDEX function. In this, we will see the usage of the INSTR function.<\/p>\n\n\n\n

Unlike the INDEX function, the INSTR function has much more ability to specify the position from where the search can be started. Another feature of this function is that it also gives us the flexibility to find the nth occurrence of a string.<\/p>\n\n\n\n

Syntax<\/h2>\n\n\n\n
INSTR(String expression, String to search, Starting position, Nth occurrence)<\/pre>\n\n\n\n
String expression<\/strong> – The full string value inside which the search will be done<\/p>\n\n\n\n
String to search<\/strong> – The string to search inside the original string value<\/p>\n\n\n\n
Starting position<\/strong> – The position from where the search can be done<\/p>\n\n\n\n
Nth occurrence<\/strong> – The nth occurrence of the searched string<\/p>\n\n\n\n
Let us see an example to understand all possible usage of the INSTR function.<\/p>\n\n\n\n
Imagine you are searching for the keyword ‘ERROR’ inside a column where the application’s error message is stored. There are multiple ways in which the INSTR function can be used in this scenario.<\/p>\n\n\n\n
Case 1 – With two arguments<\/h2>\n\n\n\n
SELECT INSTR('ERROR: Your query failed with SYNTAX ERROR ','ERROR') pos;<\/pre>\n\n\n\n
In the above example, we are only using two arguments. The result will give the position of the word ‘ERROR’. This case is the same as the INDEX function. The result will give the position of the first occurrence of the word ‘ERROR’<\/p>\n\n\n\n
Output – Case 1<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Case 2 – With three arguments<\/h2>\n\n\n\n
SELECT INSTR('ERROR: Your query failed with SYNTAX ERROR ','ERROR', 3) pos;<\/pre>\n\n\n\n
In the above query,  you can see that we have used three arguments. The third argument will tell the query to start the search from the mentioned position. If you notice, there are two places where the word ‘ERROR’ is present in the string. One is at the first position and the other is at the 38th position. Since we have given the starting position of the search as 3, it will ignore the first occurrence of the word ‘ERROR’ which is at the first position.<\/p>\n\n\n\n
Output – Case 2<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The same query with position value as 1 will return the output as 1 as we have the word ‘ERROR’ present at the first position.<\/p>\n\n\n\n
SELECT INSTR('ERROR: Your query failed with SYNTAX ERROR ','ERROR', 1) pos;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Case 3 – With four arguments<\/h2>\n\n\n\n
SELECT INSTR('ERROR: Your query failed with SYNTAX ERROR ','ERROR', 1, 2) pos;<\/pre>\n\n\n\n
In this query, we have included all the possible number of arguments supported by the INSTR function. The fourth argument here represents the Nth occurrence of the word that we are searching for.<\/p>\n\n\n\n
The 4th argument is given as 2. This means, that we are searching for the second occurrence of the word ‘ERROR’ starting from the first position.<\/p>\n\n\n\n
Output – Case 3<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In the same example, if you change the fourth argument value to 1, you will get the output position as 1 as we have the first occurrence of the word ‘ERROR’ in the first position itself<\/p>\n\n\n\n
SELECT INSTR('ERROR: Your query failed with SYNTAX ERROR ','ERROR', 1, 1) pos;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Also read,<\/p>\n\n\n\n
  1. INDEX function in Teradata<\/a><\/li>
  2. Concatenation operation in Teradata<\/a><\/li>
  3. Substring function in Teradata<\/a><\/li>
  4. Fetching Current Date and Time in Teradata<\/a><\/li><\/ol>\n","protected":false},"excerpt":{"rendered":"
    Teradata has multiple functions to work with strings. INSTR function is a very handy one when it comes to search a string inside another. In the other blog, we saw how this is done using the INDEX function. In this, we will see the usage of the INSTR function. Unlike the INDEX function, the INSTR\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":[184,183,186,211,235,188,182,187],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1027"}],"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=1027"}],"version-history":[{"count":3,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"predecessor-version":[{"id":1036,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/1027\/revisions\/1036"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}