SQL Character Segmentation: SQL Character Segmentation

SQL Character Segmentation: SQL Character Segmentation

For oracle, character string splitting is often encountered during string processing. Unfortunately, the SQL statement does not contain the split function. This is quite confusing for us to write SQL statements. In this case, the string is separated.

For example, for a piece of data in the field '1970-mm-265 '; I want to get the mm in it. For other languages, such as awk. Split ("120-mm-265", a, "-"); print a [2]; that's it.

1. You may think it is very simple. I just need to use substr (str, 5, 2. It's a pity that you think too simple. We process multiple data records instead of one data record. If the data below it is '12-oko-45 '. In this case. That is ko. It is not the oko we want.

2. You may also think of the instr function. This function can be solved. However, you still need to borrow the sbustr function.

SELECT substr('120-mm-265',instr('120-mm-265', '-', 1, 1) + 1,instr('120-mm-265', '-', 1, 2) -(instr('120-mm-265', '-', 1, 1) + 1)) FROM   dual

Can this result be accepted?

3. Two years ago, I came up with a method. Is to borrow the ltrim, rtrim function. Because both the front and back are numbers

SELECT rtrim(ltrim('120-mm-265', '0123456789-'), '0123456789-')FROM   dual;

This can be achieved. But this case is '20m-mm-f25 '. This implementation is not scalable. So this is not taken into consideration.

4. Because of the three methods above, can you accept them? Not acceptable; then enter the advanced writing method.
Use regular functions.

SELECT regexp_substr ('1970-mm-265 ',' [^-] + ', 1, 2) FROM dual; -- this is recommended. SELECT regexp_replace ('1970-mm-265 ',' (. *)-(. *)-(. *) ',' \ 2') FROM dual;

I believe these two methods can satisfy you. If you are not satisfied yet, the regular function is only available in 10 Gb. If the version is earlier than 10 Gb, what should I do? Let's take a look.

5. Custom function split. Oracle does not provide the built-in function split. Then it is customized.

 CREATE OR REPLACE FUNCTION splitstr         (    v_desc   VARCHAR2(200),    v_icount NUMBER) RETURN VARCHAR IS    v_result  VARCHAR(20);    v_pos     NUMBER(2);    v_str     VARCHAR(1000);    v_count_2 NUMBER(2);    v_word    VARCHAR(20);BEGIN    v_result  := '';    v_str     := v_desc || ',,,';    v_count_2 := 0;    v_pos     := instr(v_str, ',');    WHILE v_pos > 1    LOOP        v_count_2 := v_count_2 + 1;        v_word    := substr(v_str, 1, v_pos - 1);        IF v_icount = v_count_2 THEN            v_result := v_word;            RETURN v_result;        ELSE            IF v_icount < v_count_2 THEN                RETURN '';            ELSE                IF v_str <> ',' THEN                    v_str := substr(v_str, v_pos + 1);                    v_pos := instr(v_str, ',');                END IF;            END IF;        END IF;    END LOOP;    RETURN v_result;END splitstr

This is how we execute
SELECT splitstr (replace ('1970-mm-265 ','-',', '), 2) from dual
In this way, the execution is OK.
If there is a better method, you can add it later. If you have a better method, you can also add it.

SQL split string

See what database you are.
SQL Server does not support regular-expression-like splitting. Only string functions are supported.
LEFT (name, CHARINDEX ('.', name, 0)
The middle part can be implemented using SUBSTRING instead of LEFT.

SQL split string

Select SUBSTRING (column A, 1, CHARINDEX (', column A) from Table
This can take out the characters before spaces in column.
Update
Update table A set Column B = (select SUBSTRING (column A, 1, CHARINDEX (', column )))