I'm trying to form a SQL query, using SQL Server 2014 without creating a function. I do not have permissions on the database to create functions so I have to do it with a query only.
I have a column named Test with the example value of:
Accounting -> Add Missing functionality in Payable -> Saving a blank Missing row
I want my query to return the information (of varying length) between the two arrows (->). I have tried the right, left, substring, charindex and patindex functions and various combinations of each.
Basically the query needs to be SUBSTRING(Test, CHARINDEX(' -> ', TEST) +3, <some length here>)
The length is the part I'm having a hard time figuring out. I need the full length minus the first part before and including the first -> which evaluates to:
Add Missing functionality in Payable -> Saving a blank Missing row
From that result, I need to remove everything after and including the ->, which would then leave me with:
Add Missing functionality in Payable
At the end of the day, I want to split this one column up into 3 like so:
Domain | Feature | Test
------------------------------------------------------------------------------
Accounting | Add Missing functionality in Payable | Saving a blank Missing row
Can anyone show me how to do this query, without having to write a function? Any suggestions would be greatly appreciated as I have been working on this one portion of the query for the better part of 4 hours now. Thank you in advance for your help. Have a great day!!
I tried the following query and it is woking fine for me:
DECLARE @X as varchar(1000)
SET @X = 'Accounting -> Add Missing functionality in Payable -> Saving a blank Missing row'
SELECT SUBSTRING(@X,1,CHARINDEX('->',@X) - 1) AS Domain,
SUBSTRING(@X,CHARINDEX('->',@X) + 2,LEN(SUBSTRING(@X,CHARINDEX('->',@X) + 2,LEN(@X))) - LEN(SUBSTRING(@X,LEN(@X) - CHARINDEX('>-',REVERSE(@X)) ,LEN(@X)))) AS Feature,
SUBSTRING(@X,LEN(@X) - CHARINDEX('>-',REVERSE(@X)) + 2 ,LEN(@X)) AS Test
You have to use this query:
SELECT SUBSTRING([Test],1,CHARINDEX('->',[Test]) - 1) AS Domain,
SUBSTRING([Test],CHARINDEX('->',[Test]) + 2,LEN(SUBSTRING([Test],CHARINDEX('->',[Test]) + 2,LEN([Test]))) - LEN(SUBSTRING([Test],LEN([Test]) - CHARINDEX('>-',REVERSE([Test])) ,LEN([Test])))) AS Feature,
SUBSTRING([Test],LEN([Test]) - CHARINDEX('>-',REVERSE([Test])) + 2 ,LEN([Test])) AS Test
FROM MyTable --Replace MyTable with your table name