Would like to separate out text into two columns

Will Faulkner 201 Reputation points
2020-12-07T08:12:46.367+00:00

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

No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,501 Reputation points
    2020-12-07T08:21:12.91+00:00

    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


0 additional answers

Sort by: Most helpful