How to split a string having values "." seperated in SQL-CodePudding

For Eg; abc.def.efg , separate into independent strings as abc def efg

Head
abc.def.efg

left	center	right
abc	def	efg

CodePudding user response：

On SQL Server with a 3-part delimited string you can use parsename

with t as (
  select 'left.centre.right' Head
)
select ParseName(Head,3) L, ParseName(Head,2) C, ParseName(Head,1) R
from t;

CodePudding user response：

on MySQL, you can do:

with t as (
   select 'left.centre.right' Head
 )
 select 
   substring_index(Head,'.',1) as L,
   substring_index(substring_index(Head,'.',2),'.',-1) as M,
   substring_index(Head,'.',-1) as R
 from t;

results:

L	M	R
left	centre	right

see: DBFIDDLE, and DOCS

CodePudding user response：

Look into the split_part() equivalent for the RDBMS you're using.

E.g.

SELECT 
    split_part(Head, '.', 1) AS "left",
    split_part(Head, '.', 2) AS center,
    split_part(Head, '.', 3) AS "right"
FROM your_table

EDIT: corrected the indexes, see: https://www.postgresqltutorial.com/postgresql-split_part/