一列分成多列方法:
1.SELECT REGEXP_SUBSTR(name,'^[^ ]*') first_name, REGEXP_SUBSTR(name, '([[:alpha:]]+)$') middle_name FROM CELEBRITY
2.SELECT SUBSTR(NAME, 1, INSTR(NAME, ' ')-1) FIRST_NAME, SUBSTR(NAME, INSTR(NAME, ' ')+1) MIDDLE_NAME FROM CELEBRITY
3.SELECT
SUBSTR(NAME, 1, SPACE_POS-1) FIRST_NAME,
SUBSTR(NAME, SPACE_POS+1) MIDDLE_NAME
FROM (SELECT NAME,INSTR(NAME, ' ') SPACE_POS
FROM CELEBRITY)
行转列
select t.user_name as 姓名,
MAX(decode(t.course,'语文',score,null)) as 语文,
MAX(decode(t.course,'数学',score,null)) as 数学,
MAX(decode(t.course,'英语',score,null)) as 英语
from RowToCol t group by t.user_name order by t.user_name
*当要分组的值,如例子中的score列是数值型,则聚集函数可以使用sum/max/min/avg,但是字符行的只能使用max/min。
取小数点值 select regexp_substr('12.12元/小时', '\d*(\d*\.\d*)?') from dual