Split string in two or three columns based on character count

Transact-SQL
1

Hi All

I have a table that contains one column called Symbol. The symbol consists of two or three parts. They are ticker, indicator and dimension or in some cases only ticker and indicator. They are split by an underscore. I have written a script to split these parts into different columns and it works fine for the symbol values that contain two underscore but not for the one that contains 1 underscore as it places the indicator value in the dimension column. I was hoping somebody could help me with fixing this. So for example the symbol value of AAPL_DEBT_MRQ gets split between Ticker Indicator and Dimension as follow AAPL, DEBT,MRQ. This is how it should be. But for AAPL_DIVYIELD it gets split between Ticker and Dimension putting no value in indicator which is where it actually should be.

Below is my code: Sorry I did try to format this but couldn't work out how.

SELECT TOP 100 [Symbol],
LEFT([Symbol], CHARINDEX('', [Symbol])-1) AS [Ticker],
LEFT(SUBSTRING([Symbol], CHARINDEX('', [Symbol])+1, LEN([Symbol])), CHARINDEX('', SUBSTRING([Symbol], CHARINDEX('', [Symbol])+2, LEN([Symbol])))) AS Indicators,
RIGHT([Symbol], (CHARINDEX('', REVERSE([Symbol]), 0))-1) AS Dimension
FROM [dbo].[STG_Quandl_Core_US_Fundamentals]
WHERE LEFT([Symbol], CHARINDEX('', [Symbol])-1) = 'AAPL'
ORDER BY LEFT(SUBSTRING([Symbol], CHARINDEX('', [Symbol])+1, LEN([Symbol])), CHARINDEX('', SUBSTRING([Symbol], CHARINDEX('_', [Symbol])+2, LEN([Symbol])))) ASC,
Dimension ASC;

2 
SELECT
	a.a AS Symbol,
	LEFT(a,CHARINDEX('_',a+'_')-1)  Ticker,
	LEFT(b,CHARINDEX('_',b+'_')-1)	Indicator,
	LEFT(c,CHARINDEX('_',c+'_')-1)	Dimension
FROM
	(SELECT Symbol AS a FROM [dbo].[STG_Quandl_Core_US_Fundamentals]) AS A
	CROSS APPLY (VALUES (STUFF(a,1,CHARINDEX('_',a+'_'),''))) B(b)   
	CROSS APPLY (VALUES (STUFF(b,1,CHARINDEX('_',b+'_'),''))) C(c)

See here for an explanation of the logic used (responses from me and @Kristen )

3

Thanks James! Sorry for the late response!