- String In String Functions
- How POSITION Works
- Using POSITION Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
How POSITION Works
Because POSITION is complex, let's go through it step by step, using Sybase's Adaptive Server Anywhere (ASA) LOCATE function so we'll have some code to look at. (If you follow along on your own RDBMS, be sure to check the documentation for the name and syntax of the equivalent function—arguments may not be in the same order.) The first few examples don't have much real-world use; they just illustrate how the function works in isolation. Later examples show LOCATE used as an argument to another function, which is its real strength.
The following query finds the position of the first occurrence of "45" in the empnum column.
select fname, empnum, locate (empnum, '45') from employee fname empnum locate(employee.empnum,'45') ==================== ========= ============================ Hamid 123232345 8 laurna 223232366 0 ruby 111223333 0 lauren 923457789 4 Bill 222222221 0 Scorley 443232366 0 [6 rows]
If you want to find employees with "45" somewhere in their employee numbers, you can use LIKE. To find employee numbers with the first occurrence of "45" somewhere in the second half of the number you could use code like this:
select fname, empnum from employee where locate (empnum, '45') > 3 fname empnum ==================== ========= Hamid 123232345 lauren 923457789 [2 rows]