How to divide the text into two columns by matching text using SQL select query-CodePudding

I have table with a column Note / Reason - like this:

Note / Reason
test
test2
test  REASON:ANOTHER PROVIDER
this is a test  REASON:NO FITTER

I want to divide the text like below into note and reason like below reason with start with reason and other text will be note

Note                        Reason
---------------------------------------------------
test
test2'
test                        REASON:ANOTHER PROVIDER
this is a test              REASON:NO FITTER

CodePudding user response：

Using Sean's sample data, as I mentioned in the comments, use CHARINDEX, LEFT and STUFF:

SELECT LEFT(NoteReason,CHARINDEX('REASON:',NoteReason   'REASON:')-1) AS Note,
       STUFF(NoteReason,1,CHARINDEX('REASON:',NoteReason)-1,'') AS Reason
FROM @NoteReason;

Considering you have extra white space, you may also want to wrap the expressions in a TRIM.

CodePudding user response：

It would be better if you provided sample data in a consumable format. That way it is easy for others to use and it is also more precise so others aren't guessing or making assumptions about your tables and sample data. Given the sparse information in your question something like this should be somewhat close.

declare @NoteReason table (NoteReason varchar(100))
insert @NoteReason values
('test')
, ('test2')
, ('test  REASON:ANOTHER PROVIDER')
, ('this is a test  REASON:NO FITTER')

select Note = case when charindex('REASON', n.NoteReason) = 0 then n.NoteReason
        else left(n.NoteReason, charindex('REASON', n.NoteReason) - 1)
        end
    , Reason = case when charindex('REASON', n.NoteReason) > 0 then substring(n.NoteReason, charindex('REASON', n.NoteReason), len(n.NoteReason)) else '' end
from @NoteReason n