JR8305
April 12, 2018, 10:18am
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
Ifor
April 12, 2018, 12:52pm
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);
JR8305
April 12, 2018, 2:00pm
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.
Ifor
April 12, 2018, 6:57pm
4
The most likely problem is that Textcol is not consistant. You will have to look at the data and work it out.