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
