How to cut or isolate text up to a certain character with sql query?

Question

MySQL

There is a type table:
Name of
Pupkin Plant "Rosmoscom"
JSC "Shchukin's Enterprise "Portik" branch"
ZAO Lomonosov Research Institute "Gorsvet"
Need to get:
Name
"Rosmoskom" "Portik"
" Gorsvet
"
the first or last quote.How
to specify the number of the character ("), so that it is possible to receive adequate answers and is it possible to create a construction like if, else if, else if, else in order to write all the conditions in the query? Are there other solutions to this problem?

Answer 1

It looks like there is no regular expression replacement in Mysql, so I wrote this monster (I'm not strong in MySQL syntax):

SELECT case when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 2 
                 then SUBSTRING(TEXT FROM LOCATE('"', TEXT)+1 FOR CHARACTER_LENGTH(TEXT) - LOCATE('"', TEXT)-1)
            when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 3    
                 then SUBSTRING(TEXT FROM LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1 FOR CHARACTER_LENGTH(TEXT)-LOCATE('"', TEXT,LOCATE('"', TEXT)+1)-1 )
            when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 4 
                 then SUBSTRING(TEXT FROM LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1 FOR CHARACTER_LENGTH(TEXT)-LOCATE('"', TEXT, LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1)-2)
            else null     
       end 
FROM  mytable

or like this:

select case when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 2 
                  then SUBSTRING_INDEX(SUBSTRING_INDEX(TEXT, '"', 2), '"', -1)
            when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) > 2  
                 then SUBSTRING_INDEX(SUBSTRING_INDEX(TEXT, '"', 3), '"', -1)
            else null
       end   
  FROM  mytable

Answer 2

Regular expressions.