text data extraction with a sql query

  • Hi,

    i use a sql 2005 database where we stored phone call historic notes. The detail of the phone call is stored on a a field from table called:

    Table: 'AMGR_Notes_Tbl'

    Field: 'TextCol'

    The text charcter in the 'Textcol''s fieldlooks like this:

    "Spoke with Joe Adams. i told him ti schedule a demo meeting.

    Phone Call: Aggassi Cold Beer & Wine Store

    Number Called: Incoming Call

    Subject: Cold call

    Result: Arranged interview

    Duration: 00:00:18"

    What i need:

    I need to obtain this column:

    Subject | Result | Duration |

    Do you have an idea to design a query to obtain this result?

    Regards,

    Ozzy

  • If you are guaranteed a consistent order in the data this works:

    Declare @AMGR_Notes_Tbl table (textcol varchar(max))

    Insert Into @AMGR_Notes_Tbl

    Select

    'Spoke with Joe Adams. i told him ti schedule a demo meeting.'+

    'Phone Call: Aggassi Cold Beer & Wine Store' +

    'Number Called: Incoming Call' +

    'Subject: Cold call' + CHar(10) + Char(13) +

    'Result: Arranged interview' +

    'Duration: 00:00:18'

    Select

    Substring(textcol, CharIndex('Subject', TextCol) + Len('Subject') + 1,

    CharIndex('Result', TextCol)-CharIndex('Subject', TextCol) - Len('Subject') - 1) as subject,

    Substring(textcol, CharIndex('Result', TextCol) + Len('Result') + 1,

    charIndex('Duration', TextCol) - CharIndex('Result', TextCol) - Len('Result')-1) as result,

    Substring(textcol, CharIndex('Duration', TextCol) + Len('Duration') + 1,

    Len(TextCol) - CharIndex('Duration', TextCol)) as duration

    From

    @AMGR_Notes_Tbl A

  • Thanks very much jack.

    it works like a charm.

    Great help.

    regards,

    Ozzy

  • Re-Hi Jack,

    How I can modify the query to obtain the result with the same column but with all the value from a type of phone call note.

    The table 'AMGR_Notes_Tbl' in sql server 2005 stored all the notes we create and modify for aour application. Notes are organised by 'Type' as 'Manual', 'Phone Call', 'History'. The 'phone call' type is equal '2' so if i want to retrieve all the 'phone call' notes, I use this query,

    'select Type, TextCol, DateCol from AMGR_Notes_tbl where type =2'

    SO, the thing that I would obtain is a query that give me all the values all the phonecall notes and displayed as:

    DateCol |Subject | Call result | Duration |

    (* the DateCol is a datetime field)

    Any idea?

    Many thanks in advance,

    Ozzy

  • That would be basically the same query I gave you with datecol added to the output and a where clause.

  • Hi Jack,

    I ad the 'DateCol' at the output but it doesn'work (name of column non valid 'DateCol').

    Also, i cannot retrieve all the phone call notes. The query only extract term from one note and not for all.

    Any idea?

    Regards,

    Ozzy

  • jacopasto (10/28/2008)


    I ad the 'DateCol' at the output but it doesn'work (name of column non valid 'DateCol').

    Sounds like you have spelled the column name wrong, check that.

    Also, i cannot retrieve all the phone call notes. The query only extract term from one note and not for all.

    Huh?

    Please read the articles linked in my signature line and post according to the suggestions. I can't do anymore without the information mentioned in the articles.

  • Ok Jack,

    My Notes table looks like this:

    Data_Machine_Id Sequence_Number Owner_Id Client_Id Contact_Number Type Private DateCol TimeCol NewRecord Owned_By_Id Record_Id Note_Type TextCol

    --------------------------------------- --------------------------------------- ------------ ------------------------ -------------- ----------- ----------- ----------------------- ----------------------- ----------- ------------ ----------- -------------------- -------- ------------ ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    772238169 1 MASTER 001124000913063671156C 0 2 0 2002-10-08 00:00:00.000 1900-01-01 09:12:52.000 65535 MASTER 11 0 Spoke with Joe Adams. i told him ti schedule a demo meeting. Phone Call: Aggassi Cold Beer & Wine Store Number Called: Incoming Call Subject: Cold call Result: Arranged interview Duration: 00:00:18

    772238169 2 MASTER 001124000913063671156C 0 2 0 2003-12-07 00:00:00.000 1900-01-01 20:55:20.000 65535 12 0 Phone Call: Aggassi Cold Beer & Wine Store. Number Called: 1 250 988.0089Phone Description: Main Result: Left Message Duration: 00:00:00

    It is just a sample with two notes.

    I just need to obtain these data as:

    Date | Subject | Call subject | Duration

    10/08/2003 arranged interview cold call 00:00:18

    12/07/2002 Left message 00:00:00

    Is it more convenient?

    Many thanks.

    Regards,

    Ozzy

  • This should work:

    [font="Courier New"]DECLARE @AMGR_Notes_Tbl TABLE (Data_Machine_Id INT, Sequence_Number INT, Owner_Id VARCHAR(10),

                                   Client_Id VARCHAR(10), Contact_Number VARCHAR(15),

                                   TYPE CHAR(1), Private INT, DateCol smalldatetime,

                                   TimeCol smalldatetime, NewRecord INT, Owned_By_Id VARCHAR(10),

                                   Record_Id  INT, Note_Type INT, TEXTcol VARCHAR(MAX))

            

    INSERT INTO @AMGR_Notes_Tbl

            SELECT

                   772238169 AS Data_Machine_ID,

                   1 AS Sequence_number,

                   'MASTER' AS owner_id,

                   '001124000' AS client_Id,

                   '913063671156C' AS contact_number,

                   0 AS TYPE,

                   2 AS private,

                   '2002-10-08 00:00:00.000',

                   '1900-01-01 09:12:52.000',

                   65535,

                   'MASTER',

                   11,

                   0,          

                    'Spoke with Joe Adams. i told him ti schedule a demo meeting.'+

                    'Phone Call: Aggassi Cold Beer & Wine Store' +

                    'Number Called: Incoming Call' +

                    'Subject: Cold call' +

                    'Result: Arranged interview' +

                    'Duration: 00:00:18'

               UNION ALL

               SELECT

                   772238169,

                   2,

                   'MASTER',

                   '001124000',

                   '913063671156C',

                   0,

                   2,

                   '2003-12-07 00:00:00.000',

                   '1900-01-01 20:55:20.000',

                   65535,

                   'MASTER',

                   12,

                   0,

                   'Phone Call: Aggassi Cold Beer & Wine Store.' +

                   ' Number Called: 1 250  988.0089 ' +

                   'Phone Description: Main   ' +

                   'Result: Left Message ' +

                   'Duration: 00:00:00'

    SELECT

           DateCol,

            CASE

               WHEN CHARINDEX('Subject', TEXTCol) > 0 THEN

                       SUBSTRING(TEXTcol, CHARINDEX('Subject', TEXTCol) + LEN('Subject') + 1,

                        CHARINDEX('Result', TEXTCol)-CHARINDEX('Subject', TEXTCol) - LEN('Subject') - 1)

               ELSE NULL

           END AS subject,

            CASE

               WHEN CHARINDEX('Result', TEXTCol) > 0 THEN

                       SUBSTRING(TEXTcol, CHARINDEX('Result', TEXTCol) + LEN('Result') + 1,

                        CHARINDEX('Duration', TEXTCol) - CHARINDEX('Result', TEXTCol) - LEN('Result')-1)

               ELSE NULL

           END AS result,

            CASE

               WHEN CHARINDEX('Duration', TEXTCol) > 0 THEN

                       SUBSTRING(TEXTcol, CHARINDEX('Duration', TEXTCol) + LEN('Duration') + 1,

                        LEN(TEXTCol) - CHARINDEX('Duration', TEXTCol))

               ELSE NULL

           END AS duration

    FROM

            @AMGR_Notes_Tbl A

    [/font]

    Also you may want to watch the width of your posts (code blocks and quotes) as most folks dislike the horizontal scroll.

  • Many thanks Jack.

    The sql retrieve the two notes. How i can design the query to retrieve more than two the notes (the text is different as the call result's value, the Duration and the subject)?

    regards,

    Ozzy

  • jacopasto (10/29/2008)


    Many thanks Jack.

    The sql retrieve the two notes. How i can design the query to retrieve more than two the notes (the text is different as the call result's value, the Duration and the subject)?

    regards,

    Ozzy

    You need to be more specific. Do you mean that the identifier (i.e., "Duration") is different? As I said in my first post, if you are guaranteed to have the same format and order

    then my query works other wise you need to do something different.

    In reality the design stinks. You should have columns for each of the properties, subject, duration, etc..., that you enter the data in instead of a huge varchar column.

  • Hi,

    To be more specific, the format and the order of the Textcol column could be different but 'Duration', 'call result', 'Object' are nots stored in individual column but in a Text column. Only the Datecol is stored in a single column.

    So for each phone call notes (Type=2), i need to extract the 'Call result' value (after the =), the 'Duration' and the 'Object='.

    Do You need that i send you a picture of the table?

    Thnks for your help.

    Ozzy

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply