SQLTeam.com | Weblogs | Forums

Separating a Text Column into three different columns using select statement

sql2014

#1

i have a column that is a Text column with the following info:

Note: Outgoing Call
Phone Call: Johnson, Ed
Number Called: (604) 601-8000
Phone Description: Main
Subject: Arrange trip
Result: Discussed opportunities
Duration: 00:00:22

I want to know if it is possible to get the Subject and Result in two separate columns?
is this possible using a select case statement?

i want the result to look like this:

Subject | Result | Note

i have this which works however if the text is longer than what i have put in below - how do i then see all text

SELECT "Note"=SUBSTRING(TextCol,1,25),"Subject"=SUBSTRING(TextCol,110,25)
,"Result"=SUBSTRING(TextCol,135,33) FROM AMGR_Notes where type = 2

Could someone please point me in the right direction and i will right the query


#2
-- *** Test Data ***
CREATE TABLE #t
(
	TextCol varchar(MAX) NOT NULL
);
INSERT INTO #t
VALUES('Note: Outgoing Call
Phone Call: Johnson, Ed
Number Called: (604) 601-8000
Phone Description: Main
Subject: Arrange trip
Result: Discussed opportunities
Duration: 00:00:22');
-- *** End Test Data ***

SELECT
	RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(T.TextCol, A.SubjectStart + 8, A.ResultStart - A.SubjectStart - 8), CHAR(10), ''), CHAR(13), ''))) AS [Subject]
	,RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(T.TextCol, A.ResultStart + 7, A.DurationStart - A.ResultStart - 7), CHAR(10), ''), CHAR(13), ''))) AS Result
	,RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(T.TextCol, 6, A.PhoneStart - 6), CHAR(10), ''), CHAR(13), ''))) AS Note
FROM #t T
	CROSS APPLY
	(
		VALUES
		(
			CHARINDEX('Phone Call:', T.TextCol)
			,CHARINDEX('Subject:', T.TextCol)
			,CHARINDEX('Result:', T.TextCol)
			,CHARINDEX('Duration:', T.TextCol)

		)
	) A (PhoneStart, SubjectStart, ResultStart, DurationStart);

#3

Thank you, it works perfectly with Test data however if i apply the above in my query it spits out:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.


#4

The most likely problem is that Textcol is not consistant. You will have to look at the data and work it out.