I can not find a beautiful solution for isolating words from a string. For example:

SELECT 'Фамилия' as Col_1, Parse(FullName, ' ')[0] as FirstName, 'Имя' as Col_2, Parse(FullName, ' ')[1] as Name, 'Отчество' as Col_3, Parse(FullName, ' ')[2] as Patronymic FROM Students

Is there anything like this in SQL without writing your own stored functions?

  • Well, a simple division by space, leaving the group with a known number is easy to organize. The same REGEXP_REPLACE() for example ... - Akina
  • I read now about regular expressions. Powerfully. I do not quite understand how to use it yet, but I think it is possible to pull out the right word through the templates. It only confuses me that every time a function will be called, rather than once per line, parse and then pull out the desired element. Well, okay, and soooo come. Probably. :) - Vitaly Jandulov
  • "instead of once per line, parse" - do it once in a subquery and then pull out the necessary element, nothing interferes. - 0xdb
  • It is not entirely clear - “to isolate only one word from a string” - and right there in the example you are trying to get three words. - 0xdb

3 answers 3

You can do without regulars, using only substr and instr . Despite its inconspicuousness, such a solution can show a significantly higher speed compared to regulars.

 with Students as ( select level id, 'fname'||level||' mname'||level||' lname'||level fullName from dual connect by level <= 3 ) select s.*, substr(s.fullName, 1, instr(s.fullName, ' ', 1, 1) - 1 ) as fName, substr(s.fullName, instr(s.fullName, ' ', 1, 1), instr(s.fullName, ' ', 1, 2) - instr(s.fullName, ' ', 1, 1) ) as mName, substr(s.fullName, instr(s.fullName, ' ', 1, 2) ) as lName from Students s

    It is possible so:

     with Students as ( select level id, 'fname'||level||' mname'||level||' lname'||level fullName from dual connect by level <= 3 ), params as (select '(\w+)\s+(\w+)\s+(\w+)' namePattern from dual), studentNames as ( select id, regexp_replace (fullName, namePattern, '\1') fName, regexp_replace (fullName, namePattern, '\2') mName, regexp_replace (fullName, namePattern, '\3') lName from Students, params ) select * from studentNames ; ID FNAME MNAME LNAME ---------- ---------- ---------- ---------- 1 fname1 mname1 lname1 2 fname2 mname2 lname2 3 fname3 mname3 lname3

      The answer is based on @Akina's advice (only REGEXP_SUBSTR instead of REGEXP_REPLACE).

       SELECT REGEXP_SUBSTR(FULL_NAME,'[^ ]+',1,1) AS SURNAME ,REGEXP_SUBSTR(FULL_NAME,'[^ ]+',1,2) AS NAME ,REGEXP_SUBSTR(FULL_NAME,'[^ ]+',1,3) AS PATRONYMIC FROM STUDENTS