Contents Index LIST function [Aggregate] LOG function [Numeric]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

LOCATE function [String]


Function 

Returns the position of one string within another.

Syntax 

LOCATE ( string-expression-1string-expression-2 [, integer-expression ] )

Parameters 

string-expression-1    The string to be searched.

string-expression-2    The string to be searched for. This string is limited to 255 bytes.

integer-expression    The character position in the string to begin the search. The first character is position 1. If the starting offset is negative, the locate function returns the last matching string offset rather than the first. A negative offset indicates how much of the end of the string is to be excluded from the search. The number of bytes excluded is calculated as (-1 * offset) -1.

Usage 

If integer-expression is specified, the search starts at that offset into the string.

The first string can be a long string (longer than 255 bytes), but the second is limited to 255 bytes. If a long string is given as the second argument, the function returns a NULL value. If the string is not found, 0 is returned. Searching for a zero-length string will return 1. If any of the arguments are NULL, the result is NULL.

If multi-byte characters are used, with the appropriate collation, then the starting position and the return value may be different from the byte positions.

Standards and compatibility 
Example 

The following statement returns the value 8.

SELECT LOCATE(
   'office party this week - rsvp as soon as possible',
   'party',
   2 )

The following statement:

BEGIN
   declare str long varchar;
   declare pos int;
   set str = 'c:\test\functions\locate.sql';
   set pos = locate( str, '\', -1 );
   select str, pos,
      substr( str, 1, pos -1 ) as path,
      substr( str, pos +1 ) as filename;
END

returns the following output:

str pos path filename c:\test\functions\locate.sql 18 c:\test\functions locate.sql


Contents Index LIST function [Aggregate] LOG function [Numeric]