Shulou(Shulou.com)06/01 Report--

This article introduces how to use SQL to split the field into multiple lines, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Split a field in the table into N strings according to the delimiter, and then use the N strings to transform the line into N lines.

Using SQL to solve this problem is very cumbersome!

The collection object is not provided in SQL, and the operation of turning one line into multiple lines based on the split string collection is not provided. The idea to solve this problem is to first find out the maximum number of strings M after the field is split, and then construct a temporary table T2 with M rows and 1 column, whose column name is lv, then the LV values of each row are 1, 2,... , M, and then multiply the original table with it, and take the T2.lv string after the field is split. The SQL written in this way is made up of multiple subqueries, and its syntax is more complex. And the function of splitting strings in various databases is not uniform, so the writing of SQL is also different.

For example: the COURSES data sheet of the existing student elective courses is as follows, which requires finding out how many courses each student has taken:

COURSESTUDENTSChineseTom,Kate,John,JimmyRussiaTom,Cart,JimmySpanishKate,Joan,CartPortugueseJohn,TomHistoryTom,Cart,KateMusicKate,Joan,Tom

The output is required as follows:

STUDENTNUMTom5Kate4Cart3Jimmy2Joan2John2

Take Oracle, for example, and write it in SQL as follows:

SELECT STUDENT, COUNT (*) NUM FROM

(SELECT T1.COURSE, REGEXP_SUBSTR (T1.STUDENTS,'[^,] +', 1, T2.LV) STUDENT

FROM COURSES T1

(SELECT LEVEL LV

FROM (SELECT MAX (REGEXP_COUNT (A.STUDENTS,'[^,] +', 1)) R_COUNT

FROM COURSES A

) B

CONNECT BY LEVEL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.