Would like to separate out text into two columns

Question

Hello

I am using SQL Server 2014

I have a report that returns this data into one field:

COLUMN HEADING 1

any sort of text here

WHAT TIME DID IT HAPPEN: further text here

what I'd like to do is separate this text so where 'WHAT TIME DID IT HAPPEN:' and the text following that appears in a separate column.

Desired output would be

COLUMN HEADING 1 COLUMN HEADING 2

any sort of text WHAT TIME DID IT HAPPEN: further text here*

note that there are no text limits but 'WHAT TIME DID IT HAPPEN:' always appears after the first bit of text is input and the text that follows 'WHAT TIME DID IT HAPPEN:' also has no limit.

Please can you help /advise as to how I can do this?

Many thanks

Answer 1

Hi @Will Faulkner ,

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Try：

        select left(COLUMNHEADING1,charindex(':',COLUMNHEADING1)) COLUMN HEADING1,  
               right(COLUMNHEADING1,len(COLUMNHEADING1)-charindex(':',COLUMNHEADING1)) COLUMNHEADING2  
        from yourtable  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table