'Separate cell value and take part of it

I'm looking for a solution for my problem. I have a table, and there is column, which contains words separate with dots. I want to take only one part of that word. I know about Substring_Index but my problem is, that word i want to take is on random position. e.g Project1.Test1 Project1.Subproject1.Test2 Test21.Project4 I want to take only part with Test word and number. How can i take it from whole cell value

mysql


Solution 1:[1]

Assuming that you have a table Projects with column Project:

WITH Projects AS (
    SELECT CONCAT( Project , '.' ) AS project, LOCATE('Test', Project) AS Position
    FROM Projects)
SELECT Project, substring(Project,Position,LOCATE('.',Project,Position)- Position) 
from Projects;

Here's the same query without the CTE:

SELECT Project, substring(Project,Position,LOCATE('.',Project,Position)- 
   Position) 
FROM (SELECT CONCAT(Project,'.') AS Project, LOCATE('Test',Project) AS 
  Position
FROM Projects) AS Projects;

In the CTE, add a dot and find the position of the word Test. In the outer query return the string starting at the Position and find the dot after that position. Subtract the two positions.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions