The LOCATE SQL Function in MySQL and MariaDB - Search in string from position |
|
| LOCATE | Syntax: | LOCATE(Search string, String [, Position]) | Return value: | INTEGER | Function type: | String function | |
| | The LOCATE() function returns the position of the first occurrence of "Search string" in "String", depending on the specification of "Position".
If "Position" is specified, the function searches for the "Search string" from this position.
If "Search string" is not found or "Position" is negative, the function returns 0.
If "Search string" is empty, the result is 1.
If "String" or "Search string" contain numeric values, these are interpreted as character strings.
The LOCATE() function performs a case-sensitive search.
If either the "Search string" or "String" argument is NULL, the LOCATE() function returns NULL.
If the value of "Position" is NULL, the function returns NULL in MySQL, 0 in MariaDB. | SQL Examples for the LOCATE function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
|
select locate('e','Test');
select locate('st', 'Test');
select locate('T', 'Test');
select locate('t', 'Test');
select locate(4, 123456);
select locate('e','Test-Test', 4);
select locate('st', 'Test-Test', 4);
select locate('e','Test-Test', -4);
select locate('', 'Test');
select locate('x', 'Test');
select locate('', '');
select locate(null, 'Test');
select locate('e', null);
select locate('e', 'Test', null);
|
|
locate('e','Test') |
int(11) |
2 |
|
|
locate('st', 'Test') |
int(11) |
3 |
|
|
locate('T', 'Test') |
int(11) |
1 |
|
|
locate('t', 'Test') |
int(11) |
4 |
|
|
locate(4, 123456) |
int(11) |
4 |
|
|
locate('e','Test-Test', 4) |
int(11) |
7 |
|
|
locate('st', 'Test-Test', 4) |
int(11) |
8 |
|
|
locate('e','Test-Test', -4) |
int(11) |
0 |
|
|
locate('', 'Test') |
int(11) |
1 |
|
|
locate('x', 'Test') |
int(11) |
0 |
|
|
|
locate(null, 'Test') |
int(11) |
NULL |
|
|
locate('e', null) |
int(11) |
NULL |
|
|
locate('e', 'Test', null) |
int(11) |
0 |
|
|
| The examples were created with the MyWAY SQL manager: | How to use the LOCATE() function in MySQL and MariaDB databases | In MySQL and MariaDB the LOCATE() function is used to determine the position of a substring within a string found from a given position. The function returns the index of the first occurrence of the substring after the specified position within the string, or 0 if the substring was not found. LOCATE() can be used for various purposes, such as searching for specific patterns, extracting portions of a string, or performing conditional operations based on the presence or location of a substring. | | Further MySQL und MariaDB SQL String functions | CHAR | CHAR(Character code, [, Charset]) | More about CHAR Function |
| CHR | CHR(Character code) | More about CHR Function |
| CONCAT | CONCAT(Strings ('str1','str2', ...)) | More about CONCAT Function |
| CONCAT_WS | CONCAT_WS(Separator, Strings ('str1','str2', ...)) | More about CONCAT_WS Function |
| ELT | ELT(Index, Strings ('str1','str2', ...)) | More about ELT Function |
| EXPORT_SET | EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits]) | More about EXPORT_SET Function |
| EXTRACTVALUE | EXTRACTVALUE(XML, XPath) |
| FIELD | FIELD(Search string, Strings ('str1','str2', ...)) | More about FIELD Function |
| FORMAT | FORMAT(Number, Decimals [, Region]) | More about FORMAT Function |
| INSERT | INSERT(String, Position, Length, String to insert) | More about INSERT Function |
| LCASE | LCASE(String) | More about LCASE Function |
| LEFT | LEFT(String, Length) | More about LEFT Function |
| LOAD_FILE | LOAD_FILE(File name) |
| LOWER | LOWER(String) | More about LOWER Function |
| LPAD | LPAD(String, Length, String to append) | More about LPAD Function |
| LPAD_ORACLE | LPAD_ORACLE(String, Length, String to append) | More about LPAD_ORACLE Function |
| LTRIM | LTRIM(String) | More about LTRIM Function |
| MAKE_SET | MAKE_SET(Bits, Strings ('str1','str2', ...)) | More about MAKE_SET Function |
| MID | MID(String, Position [, Length]) | More about MID Function |
| QUOTE | QUOTE(String) | More about QUOTE Function |
| REPEAT | REPEAT(String, Number) | More about REPEAT Function |
| REPLACE | REPLACE(String, Search, Replace) | More about REPLACE Function |
| REVERSE | REVERSE(String) | More about REVERSE Function |
| RIGHT | RIGHT(String, Length) | More about RIGHT Function |
| RPAD | RPAD(String, Length, String to append) | More about RPAD Function |
| RPAD_ORACLE | RPAD_ORACLE(String, Length, String to append) | More about RPAD_ORACLE Function |
| RTRIM | RTRIM(String) | More about RTRIM Function |
| SOUNDEX | SOUNDEX(String) | More about SOUNDEX Function |
| SPACE | SPACE(Number) | More about SPACE Function |
| SUBSTR | SUBSTR(String, Position, Length) | More about SUBSTR Function |
| SUBSTRING | SUBSTRING(String, Position, Length) | More about SUBSTRING Function |
| SUBSTRING_INDEX | SUBSTRING_INDEX(String, Delimiter, Number) | More about SUBSTRING_INDEX Function |
| TRIM | TRIM([Type] [, Delete character], , String) | More about TRIM Function |
| TRIM_ORACLE | TRIM_ORACLE([Type] [, Delete character], , String) | More about TRIM_ORACLE Function |
| UCASE | UCASE(String) | More about UCASE Function |
| UPPER | UPPER(String) | More about UPPER Function |
| WEIGHT_STRING | WEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting]) | More about WEIGHT_STRING Function |
| ASCII | ASCII(String) | More about ASCII Function |
| BIT_LENGTH | BIT_LENGTH(String) | More about BIT_LENGTH Function |
| CHAR_LENGTH | CHAR_LENGTH(String) | More about CHAR_LENGTH Function |
| CHARACTER_LENGTH | CHARACTER_LENGTH(String) | More about CHARACTER_LENGTH Function |
| FIND_IN_SET | FIND_IN_SET(Search string, Set of values) | More about FIND_IN_SET Function |
| INSTR | INSTR(String, Search string) | More about INSTR Function |
| LENGTH | LENGTH(String) | More about LENGTH Function |
| LENGTHB | LENGTHB(String) | More about LENGTHB Function |
| OCTET_LENGTH | OCTET_LENGTH(String) | More about OCTET_LENGTH Function |
| ORD | ORD(String) | More about ORD Function |
| POSITION | POSITION(Search string IN String) | More about POSITION Function |
|
|
| | More information about the LOCATE SQL function: and and |
|
|
|
|