Post reply

Data parse and extract from text feild in sql 2000.

  • January 23, 2012 at 6:08 pm

    #104110

    Hi

    Does any one know below text feild data how to parse and extract Agent,date/time,Product Interest,Source,Notes data into temp table.

    here is format example : agent date/time Product Interest Source Notes

    The db we are using is sql 2000 and the column in the database is text.

    Customer : 111111 Agent : ABC1 Date/Time : Oct 4 2000 10:54AM Product Interest : XX Source : Sales Follow up Notes : Should receive by the end of next week. Customer : 111111 Agent : ABC2 Date/Time : Oct 3 2000 2:59PM Product Interest : XX Source : Sales Follow up Notes : 234567 Customer : 111111 Agent : ABC3 Date/Time : Oct 3 2000 2:58PM Product Interest : XX23 Source : Sales Follow up Notes : 45678 earlier order Customer : 111345 Agent : ABC3 Date/Time : Oct 3 2000 2:57PM Product Interest : XX456 Source : Sales Follow up Notes : voided order--already placed on duplicate with postcard deiscount Customer : 232323 Agent : WEB12 Date/Time : Sep 12 2000 3:06PM Product Interest : ZXC Source : Sales Follow up Notes : Web Order ID:ASXZ12

    Thanks,

    Van

  • February 1, 2012 at 10:19 am

    #1441318

    Hello Van,

    Here is the one way to solve your problem.

    In the script below, it works if the data is exactly like you posted.

    Customer : 111111 Agent : ABC1 Date/Time : Oct 4 2000 10:54AM Product Interest : XX Source : Sales Follow up Notes : Should receive by the end of next week.

    Customer : 111111 Agent : ABC2 Date/Time : Oct 3 2000 2:59PM Product Interest : XX Source : Sales Follow up Notes : 234567

    Customer : 111111 Agent : ABC3 Date/Time : Oct 3 2000 2:58PM Product Interest : XX23 Source : Sales Follow up Notes : 45678 earlier order

    Customer : 111345 Agent : ABC3 Date/Time : Oct 3 2000 2:57PM Product Interest : XX456 Source : Sales Follow up Notes : voided order-already placed on duplicate with postcard deiscount

    Customer : 232323 Agent : WEB12 Date/Time : Sep 12 2000 3:06PM Product Interest : ZXC Source : Sales Follow up Notes : Web Order ID:ASXZ12

    I assumed the spaces are same for the remaining data you have and I organised the data to have clear picture for myself. Because the spaces can make a huge difference.

    /*Create table #costomer(addres varchar(8000))

    insert into #costomer values ('Customer : 111111 Agent : ABC1 Date/Time : Oct 4 2000 10:54AM Product Interest : XX Source : Sales Follow up Notes : Should receive by the end of next week. ')

    ,('Customer : 111111 Agent : ABC2 Date/Time : Oct 3 2000 2:59PM Product Interest : XX Source : Sales Follow up Notes : 234567 ')

    ,('Customer : 111111 Agent : ABC3 Date/Time : Oct 3 2000 2:58PM Product Interest : XX23 Source : Sales Follow up Notes : 45678 earlier order ')

    ,('Customer : 111345 Agent : ABC3 Date/Time : Oct 3 2000 2:57PM Product Interest : XX456 Source : Sales Follow up Notes : voided order-already placed on duplicate with postcard deiscount ')

    ,('Customer : 232323 Agent : WEB12 Date/Time : Sep 12 2000 3:06PM Product Interest : ZXC Source : Sales Follow up Notes : Web Order ID:ASXZ12 ')

    */

    select SubString(addres,Len('Customer :.'),PATINDEX('% Agent :%',addres)-Len(' Agent :.')-1) AS CUSTOMER

    ,SubString(addres,PATINDEX('% Agent :%',addres)+Len(' Agent :.'),PATINDEX('% Date/Time :%',addres)-PATINDEX('% Agent :%',addres)-len(' Agent :.')) AS Agent

    ,SubString(addres,PATINDEX('% Date/Time : %',addres)+Len(' Date/Time :.'),PATINDEX('% Product Interest : %',addres)-PATINDEX('% Date/Time : %',addres)-len(' Date/Time :.')) AS [Date/Time]

    ,SubString(addres,PATINDEX('% Product Interest : %',addres)+Len(' Product Interest :.'),PATINDEX('% Source : %',addres)-PATINDEX('% Product Interest : %',addres)-len(' Product Interest :.')) AS [Source]

    ,SubString(addres,PATINDEX('% Follow up Notes : %',addres)+Len(' Follow up Notes :.'),LEN(addres)+1-PATINDEX('% Follow up Notes : %',addres)-len(' Follow up Notes :.')) AS [Follow up Notes]

    from #costomer

    The script looks simple but it took 3 hours of time to write and implement them.

    Hope this helps you!!!!

    Drop a note, if This helps you.

    Thanks,

    Aluburaj.

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

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