Oralce functions substr and mysql functions substring_index

Oralce functions substr and mysql functions substring_index note ???? Recently, I occasionally saw the substring_index function in the mysql database. First, I would like to briefly introduce the substring_index function of mysql .??? Substring_index (str, delim, count) returns the substring before the delimiter delim that appears at the count in the str string, and ja

Oralce functions substr and mysql functions substring_index note ???? Recently, I occasionally saw the substring_index function in the mysql database. First, I would like to briefly introduce the substring_index function of mysql .??? Substring_index (str, delim, count) returns the substring before the delimiter delim that appears at the count in the str string, and ja

Oralce functions substr and mysql functions substring_index note

???? Recently, I occasionally saw the substring_index function in the mysql database. First, I would like to briefly introduce the substring_index function of mysql .???

Substring_index (str, delim, count) returns the substring before the delimiter delim that appears at the count in the str string, which is similar to the str. substring function in java. If count is a positive number, substring_index checks the delim location from left to right. If count is a negative number, substring_index is the position of the delim separator from the right to the left. The return value is the subscript position starting from the subscript 1 on the left of the str string. Special note: If count is equal to 0, no matter whether delim exists in str or not, you will get a blank string. As shown below

???

select substring_index('www.baidu.com', '.', 0);select substring_index('www.baidu.com', '-', 0);

??? The above is very abstract. Here is a simple example.

???

select substring_index('www.baidu.com', '.', 2);

?? 2 is a positive number. It exists in str, and the returned result is a substring between the substr (2nd-1) position from the subscript 1. The result is 'www. baidu '.

??

 select substring_index('www.baidu.com', '.', -2);

??? -2 is a negative number ,. exists in str ,. in str, the search order is from the right to the left where the first occurrence is 4, so the negative number returns the substring from the last to 4 + 1, that is, substr (str, 4 + 1), so the result is baidu.com.

??? We can see that the substring returned by substring_index is the position where the delimiter delim matches the count in str. What happens if the delimiter delim appears less than the count position in str? To test:

??

select substring_index('www.baidu.com', '.', 3);select substring_index('www.baidu.com', '.', -3);

?? After running, you can see that all the returned results are 'www .baidu.com '. Then test whether delim does not exist in str:

??

select substring_index('www.baidu.com', '-', -1);select substring_index('www.baidu.com', '-',1);

??? After running, we can see that the returned results are all 'www .baidu.com '. From the test above, we can know that the delimiter delim does not exist in str or the str itself will be returned if the number of occurrences is less than the specified count. The above is only the Test of English data, and the test of Chinese data is the same. The test is as follows:

??

Select substring_index ('I am a Chinese _ test _ data',' _ ', 2) from dual; -- I'm a Chinese _ test select substring_index ('I am a Chinese _ test _ data',' _ ',-2) from dual; -- Test _ DATA select substring_index ('I am a Chinese _ test _ data',' _ ', 0) from dual; -- blank string select substring_index ('I am Chinese _ test _ data',' _ ', 3) from dual; -- I am Chinese _ test _ DATA

??? I will not test the combination of Chinese and English.

??? Mysql has substring_index functions. Does oracle need similar functions?
I know that only substr and mysql substring_index functions are common in oracle. Some netizens know other functions of oralce. Please leave a message. Substr is also available in mysql. I will briefly describe the differences between the two.

??? First, let's briefly introduce the substr function and instr function of oracle. I will use these two functions to simulate the substring_index function of mysql later.

???

Instr (string1, string2 [, start_position [, nth_appearance]) string1 source string string2 target string. start_position start from the position of string1. The default value is 1. The string index starts from 1. Is positive. It is retrieved from left to right, negative, and right to left. Nth_appearance indicates the number of string2. this parameter is optional. The default value is 1. It cannot be a negative number and returns the start index of the string string2 that matches the condition in the source string string1.

???

Substr (string, start_position, length) string source string, that is, the truncated string. the start position of the start_position string. when start_position is greater than 0, start from the left, and when it is less than 0, check the number of characters intercepted by length from the right. it is intercepted to the last digit by default.

??? First test:

???

select instr('www.baidu.com', '.',-1,2) from dual

??? . Exists in str.-1 indicates that the search starts from the last bit. 2 indicates that the search appears twice and the returned result is 4.

??

select instr('www.baidu.com', '.',1,2) from dual

??? . It exists in str. 1 indicates the order from left to right. 2 indicates that it appears twice and the returned result is 10.

??? Here is a special Test. Test nth_appearance first. Isn't it a negative number? Let's test nth_appearance = 0.

???

select instr('www.baidu.com', '.',1,0) from dualselect instr('www.baidu.com', '.',-1,0) from dual

??? Guess what the result is? The result is ORA-01428: The parameter '0' is out of the range, indicating that nth_appearance cannot be 0 and can only be greater than 0.

??? Test start_position again, so that start_position = 0 can be viewed.

???

select instr('www.baidu.com', '.',0,1) from dualselect instr('www.baidu.com', '-',0,1) from dualselect instr('www.baidu.com', '.',0,2) from dualselect instr('www.baidu.com', '-',0,2) from dual

??? The test results are all 0. If you change start_position to> 0, the result is as follows:

???

select instr('www.baidu.com', '.',1,1) from dual--4select instr('www.baidu.com', '-',1,1) from dual--0select instr('www.baidu.com', '.',1,2) from dual--10select instr('www.baidu.com', '-',1,2) from dual--0

??? If you change start_position to <0, the result is as follows:

???

select instr('www.baidu.com', '.',-1,1) from dual--10select instr('www.baidu.com', '-',-1,1) from dual--0select instr('www.baidu.com', '.',-1,2) from dual--4select instr('www.baidu.com', '-',-1,2) from dual--0select instr('www.baidu.com', '.',-1,3) from dual--0select instr('www.baidu.com', '-',-1,3) from dual--0select instr('www.baidu.com', '.',-1,3) from dual--0select instr('www.baidu.com', '-',-1,3) from dual--0

??? It can be seen that when the target string of the instr function does not exist in str or the number of occurrences is less than the given number of occurrences, the return value is 0;

??? The same is true for testing in Chinese:

???

Select instr ('I am a Chinese _ test _ data',' _ ',) from dual; -- ORA-01428: the parameter '0' is out of the range select instr ('I am a Chinese _ test _ data',' _ ', 1, 2) from dual; -- 8 select instr ('I am a Chinese _ test _ data',' _ ', 1, 3) from dual; -- 0 select instr (' I am a Chinese _ test _ data ', '_',-1, 2) from dual; -- 5 select instr ('I am a Chinese _ test _ data',' _ ',-1, 3) from dual; -- 0

??? Next we will test substr.

???

select substr('www.baidu.com',0,4) from dualselect substr('www.baidu.com',1,4) from dual

??? The above two execution results are all 'www .'. If the above test is in mysql, the result is as follows:

???

Select substr ('www .baidu.com ',) from dual -- blank string select substr ('www .baidu.com',) from dual -- www.

??? From this we can see that the starting position of substr in mysql cannot be 0, and the results starting from 0 or starting from 1 in oracle are the same. We will continue the oracle test below.

??

select substr('www.baidu.com',1) from dualselect substr('www.baidu.com',1,22) from dual

??? The returned result is 'www .baidu.com '.

??

select substr('www.baidu.com',-1) from dualselect substr('www.baidu.com',-1,4) from dualselect substr('www.baidu.com',-1,22) from dual

??? The returned result is 'M', indicating that the substr returns from the starting position to the last digit of str.

??? Test in Chinese as follows:

???

Select substr ('I am a Chinese _ test_data', 1) from dual -- I am a Chinese _ test_data select substr ('I am a Chinese _ test_data) from dual -- I am a Chinese _ test_data select substr ('I am a Chinese _ test_data',-1) from dual -- according to select substr ('I am a Chinese _ test _ data',-) from dual -- according to select substr (' I am a Chinese _ test _ data) from dual-Data

??? From the test above, we can see that the instr function in oracle is very similar to the substring_index function in mysql. The instr function can now obtain the subscript, and substr can be used to intercept the substring and return it.

??? Mysql

???

select substring_index('www.baidu.com', '.', 2); 

??? Oralce can do this:

???

select substr('www.baidu.com', 1, instr('www.baidu.com', '.',1,2)-1) from dual

??? The instr function-1 is returned because the results returned by mysqlsubstring_index do not include. The returned results are all 'www. baidu '.

???

??? Mysql

???

select substring_index('www.baidu.com', '.', -2);

??? Oracle can do this:

???

select substr('www.baidu.com', instr('www.baidu.com', '.',-1,2) + 1, length('www.baidu.com'))from dualselect substr('www.baidu.com', instr('www.baidu.com', '.',-1,2) + 1) from dual

??? You can skip the length function, because the substr of oracle intercepts the last character of str by default.

???? The Chinese test is as follows:
???? Below Mysql:

????

Select substring_index ('I am a Chinese _ test _ data',' _ ', 1) from dual; -- I am a Chinese select substring_index (' I am a Chinese _ test _ data ', '_', 2) from dual; -- I'm a Chinese _ test select substring_index ('I am a Chinese _ test _ data',' _ ',-2) from dual; -- Test _ DATA select substring_index ('I am a Chinese _ test _ data',' _ ', 0) from dual; -- blank string select substring_index ('I am Chinese _ test _ data',' _ ', 3) from dual; -- select substring_index ('I am chinese_test_data ','. ', 3) from dual; -- I am a chinese_test_data

??? Oracle:

???

Select substr ('I am a Chinese _ test _ data', 1, instr (' I am a Chinese _ test _ data', '_',)-1) from dual -- I am a Chinese select substr ('I am a Chinese _ test _ data', 1, instr (' I am a Chinese _ test _ data', '_', 1, 2) -1) from dual -- I am a Chinese _ test select substr ('I am a Chinese _ test _ data', instr (' I am a Chinese _ test _ data ','_', -1, 2) + 1) from dual -- Test _ DATA select substr ('I am a Chinese _ test _ data', 1, instr (' I am a Chinese _ test _ data ', '_',)-1) from dual -- ORA-01428: parameter '0' out of range select substr ('I am Chinese _ test _ data', 1, instr ('I am a Chinese _ test _ data',' _ ', 1, 3)-1) from dual -- blank select substr (' I am a Chinese _ test _ data', 1, instr ('I am a Chinese _ test_data ','. ', 1, 3)-1) from dual -- blank

??? I can see that this is a problem. If you want to return the entire string when nth_appearance (> 0) is greater than the number of times the target string appears in str, you can write it like this:

???

Select substr ('I am a Chinese _ test _ data', 1, (select (case when instr (' I am a Chinese _ test _ data', '_', 1, 3) <= 1 then (select length ('I am a Chinese _ test _ data') from dual) else (instr (' I am a Chinese _ test _ data ','_', 1, 3)-1) end) from dual

??? This cannot be used when nth_appearance = 0. Further, considering that NULL is returned when nth_appearance = 0, the return of a string like ''cannot be done using substr.

???

Select substr ('I am a Chinese _ test_data', 1, (select (case when 0 = 0 then-1 else case when instr ('I am a Chinese _ test _ data',' _ ', 1, 3) <= 1 then (select length ('I am a Chinese _ test _ data') from dual) else (instr (' I am a Chinese _ test _ data ','_', 1, 3)-1) end) from dual

??? The preceding SQL statement is ugly. we can define a stored procedure for encapsulation.

????

Create or replace procedure my_substring_index (str in varchar2, delim in varchar2, in_count in int, out_str out varchar2) as v_time int; v_delim varchar2 (20); begin if str is null then out_str: = 'blank string'; elsif length (str) = 0 then out_str: = 'blank string'; end if; if delim is null then v_delim: = ''; elsif length (delim) = 0 then v_delim: = ''; else v_delim: = delim; end if; if in_count = 0 then out_str: = 'blank string '; elsif in_count> 0 then v_time: = instr (str, delim, 1, in_count); else v_time: = instr (str, delim,-1, (-1) * in_count ); end if; if v_time = 0 then out_str: = str; end if; if in_count> 0 then if v_time = 1 then out_str: = 'blank string '; elsif v_time> 1 then select substr (str, 1, v_time-1) into out_str from dual; end if; elsif in_count <0 then if v_time> = 1 then select substr (str, v_time + 1) into out_str from dual; end if; end my_substring_index;

?? The preceding Oracle test process is as follows:

??? Under Procedures under My Object, find the Stored Procedure my_substring_index, right-click and choose Test. The Oracle Test script is displayed:

???

begin  -- Call the procedure  my_substring_index(str => :str,                     delim => :delim,                     in_count => :in_count,                     out_str => :out_str);end;

?? Enter the specific value under the test script page and press F8. The returned value is displayed without any unexpected conversion.

?? If you want to write a script on your own, create an SQL file with the following content:

??

declare   v_str varchar2(50);   v_delim varchar2(20);   v_in_count number;   v_out_str varchar2(20);begin  v_str:='&str';  v_delim:='&delim';  v_in_count:=&in_count;  my_substring_index(str => v_str,                     delim => v_delim,                     in_count => v_in_count,                     out_str => v_out_str);  dbms_output.put_line('result='||v_out_str);end;

??? Run the following command line:

???

SQL> set serveroutput on;SQL> start f:/saveFile/test.sql 16  / result=www.baiduPL/SQL procedure successfully completed

??? If you want to test it in cmd window, you can:

???

SQL> set serveroutput on; SQL> var v_str varchar2 (50); SQL> var v_delim varchar2 (20); SQL> var v_in_count number; SQL> var v_out_str varchar2 (50 ); SQL> exec: v_str: = 'My name is chinese_test_data'; SQL> exec: v_delim: = '_'; SQL> exec: v_in_count: = 2; SQL> exec my_substring_index (: v_str,: v_delim,: v_in_count,: v_out_str); SQL> print v_out_str;

????? The above is a brief introduction of the oralce function substr and mysql function substring_index. The article is a bit simple, but it is original. Please indicate the source for reprinting.
????? The full text is complete. Please forgive me for writing something bad.

?

?

?

?

?

?

?

?

?

?

?

?

??

????