Split or Separator in SQL Query

 CREATE TABLE #tmp_TBL(TableName varchar(255))

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAGRDV__RAG__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAGRDV__RAG__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RARLDV__RAL__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RARLDV__RAL__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAPRILOT_Q__RAP__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAPRILOT_Q__AP__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RCHSRLEC1Q__RCH__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RCHSLEC1Q__CH__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RDLDRV__DL__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RDLDV__DL__WarningMessages')

SELECT 

[TableName]

,LEFT([TableName],CHARINDEX('__',[TableName])-1) AS LeftPart

,(RIGHT([TableName], CHARINDEX('__', REVERSE([TableName]))-1 )) AS RightPart

,SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1) AS Removed1RightPart

,SUBSTRING((SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)),1,LEN(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)) - CHARINDEX('__', REVERSE(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)))-1) AS Removed2RightParts

,REPLACE(REPLACE([TableName],REVERSE(SUBSTRING(REVERSE([TableName]),1,CHARINDEX('__',REVERSE([TableName])) +1)),''),LEFT([TableName],CHARINDEX('__', [TableName]) +1),'') AS RemovedLeftRightPart

,REPLACE(SUBSTRING((SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)),1,LEN(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)) - CHARINDEX('__', REVERSE(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)))-1),LEFT([TableName],CHARINDEX('__',[TableName])+1),'') AS Removed2RightParts1LeftPart

/*Most Usefull

=============

[TableName]='18__10_192_143_18__RCTRDEV__CTRPRROD__ObjectsbyCategories'

--,REPLACE([TableName],REVERSE(SUBSTRING(REVERSE([TableName]),1,CHARINDEX(''__'',REVERSE([TableName])) +1)),'''')

--,LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))

,RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))

,SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1) as Server_Instance_Schema

,SUBSTRING(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1),1,LEN(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1)) - CHARINDEX(''__'', REVERSE(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1)))-1)  AS Server_Instance

===========================

*/

 FROM #tmp_TBL

DROP TABLE #tmp_TBL