{"id":912,"date":"2019-06-15T01:20:29","date_gmt":"2019-06-15T09:20:29","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=912"},"modified":"2020-04-11T06:57:43","modified_gmt":"2020-04-11T14:57:43","slug":"db2-locate","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-string-functions\/db2-locate\/","title":{"rendered":"Db2 LOCATE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 LOCATE()<\/code> function to return the position at which the first occurrence of a string starts within another string.<\/p>\n\n\n\n

Introduction to Db2 LOCATE() function<\/h2>\n\n\n\n

The LOCATE()<\/code> function returns the position at which the first occurrence of a substring starts within another string.<\/p>\n\n\n\n

The following illustrates the syntax of the LOCATE()<\/code> function:<\/p>\n\n\n

LOCATE(search_string,source_string,start<\/span>,string_unit)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the function returns the starting position of the first occurrence of the search_string<\/code> within the source_string<\/code>.<\/p>\n\n\n\n
If any argument is NULL, the function returns NULL.<\/p>\n\n\n\n
search_string<\/h3>\n\n\n\n
Specify the string to be searched within the source_string<\/code>.<\/p>\n\n\n\n
  • If the search_string<\/code> is not found, the function returns zero.<\/li>
  • If the search_string<\/code> has a length of zero, the function return 1.<\/li>
  • If the search_string<\/code> found in the source_string<\/code>, the function returns a result that ranges from 1 to the actual length of the source_string<\/code>.<\/li><\/ul>\n\n\n\n
     source_string<\/h3>\n\n\n\n
    Specify the source_string<\/code> from which to find the search_string<\/code>.<\/p>\n\n\n\n
     start<\/h3>\n\n\n\n
    Is an optional integer that specifies the position in the source_string<\/code> at which the search is to begin.<\/p>\n\n\n\n
    If you omit the start, the search begins at the beginning of the source_string<\/code>.<\/p>\n\n\n\n
     string_unit<\/h3>\n\n\n\n
    Is an optional string unit that expresses in what units the start of the result of the function. The string_unit<\/code> can be one of the following value CODEUNITS16<\/code>, CODEUNITS32<\/code>, and OCTETS<\/code>.<\/p>\n\n\n\n
    Db2 LOCATE() function examples<\/h2>\n\n\n\n
    Let’s take some examples of using the LOCATE()<\/code> function.<\/p>\n\n\n\n
    1) Using Db2 LOCATE() function to find a string in another string<\/h3>\n\n\n\n
    This example uses the LOCATE()<\/code> function to find the first occurrence of the string 'is'<\/code> in the string 'This is the LOCATE function'<\/code>:<\/p>\n\n\n
     SELECT<\/span>\n      LOCATE<\/span>('is'<\/span>,'This is the LOCATE function'<\/span>)\n FROM<\/span>\n      SYSIBM.SYSDUMMY1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the result:<\/p>\n\n\n
    RESULT      \n----------- <\/span>\n3<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) Using Db2 LOCATE() function with table data example<\/h3>\n\n\n\n
    The following statement finds books whose title contains the 'Java'<\/code> keyword:<\/p>\n\n\n
     SELECT<\/span> \n      title, \n      LOCATE<\/span>('Java'<\/span>,title) java_location\n FROM<\/span>\n      books\n WHERE<\/span> \n      LOCATE<\/span>('Java'<\/span>,title) <> 0<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the partial output:<\/p>\n\n\n\n
    \"Db2<\/figure>\n\n\n\n
    3) Using Db2 LOCATE() function with string unit example<\/h3>\n\n\n\n
    This example uses the string unit as CODEUNITS32<\/code> to find the character \u00df<\/code> in the string Ma\u00df\u017ftab<\/code>:<\/p>\n\n\n
     SELECT<\/span> \n      LOCATE<\/span>('\u00df'<\/span>,'Ma\u00df\u017ftab'<\/span>,1<\/span>,CODEUNITS32) result<\/span>\n FROM<\/span>\n      SYSIBM.SYSDUMMY1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the output:<\/p>\n\n\n
     RESULT      \n ----------- <\/span>\n 3       \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, you have learned how to use the Db2 LOCATE()<\/code> function to find the first occurrence of a string within another string.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t