none
Extracting data from one column to another

    Dotaz

  • Hello,

        I'm currently using MS SQL Server 2008 and I am trying to extract a certain string of data ("Error adding Coupon:" from column "ErrorNotes2" and add it to column "Reason for OrderLink Discount" I have attached a JPEG showing a small version of the data and what the outcome should be. The problem is the string "Error adding Coupon" can be anywhere in the column "ErrorNotes2"

    Any help on this problem would be greatly appreaciated.

    Thanks

    bcl_itqa

    itqa

     

    9. dubna 2012 1:02

Všechny reakce

  • Itqa,

    Try 

      
    CREATE TABLE TESTID (ID INT , MYREASON CHAR(500))
    
    INSERT INTO TESTID VALUES(1,'THIS IS THE FIRST REASON ERROR ADDING COUPON: COUPON REQUIREMENT NOT MET.')
    INSERT INTO TESTID VALUES(2,'THANKS FOR PROVIDING REASON , AND THIS IS THE SERCOND REASON ERROR ADDING COUPON: COUPON REQUIREMENT NOT MET.')
    INSERT INTO TESTID VALUES(3,'ERROR ADDING COUPON: INVALID COUPON ID : 44789.THIS IS THE THIRD REASON ')
    INSERT INTO TESTID VALUES(4,'Thanks for calling customer care and have a nice day ahead ERROR ADDING COUPON: INVALID COUPON ID : 44741.THIS IS THE FOURTH REASON')
    
    
    SELECT * FROM TESTID
    
    SELECT SUBSTRING(MYREASON,PATINDEX('%ERROR ADDING COUPON:%',MYREASON),(select PATINDEX('%.%',MYREASON)-PATINDEX('%ERROR ADDING COUPON:%',MYREASON))) FROM TESTID
    


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    9. dubna 2012 7:36
  • Thank you Manish for your quick response. I'm not sure where I need to add this code, or do I create a brand new SQL statement?  If I create a new statement, how do I load the current ErrorNotes2 field for testing with the TESTID field?

    Here is my SQL statement to build the report that I previously attached:

    select

    ST

    .STOREID as 'Store#',

    ST

    .Name AS 'StoreName',

    DFS

    .DF_BusinessDay AS 'OrderDate',

    c

    .LastName,

    T

    .TICKETID AS 'Ticket#',

    A

    .Name AS 'ItemDescription',

    T

    .Notes AS 'Reason for OrderLink Discount?',

    T

    .Notes AS 'ErrorNotes2',

    PurchaseSubtotal

    ,

    CreditSubtotal

    ,

    ChargeSubtotal

    ,

    TaxSubtotal

    ,

    Total

    FROM

    ALLITEMS A, ORDERCREDITS OC, TICKETS T, DAYFILESUMMARY DFS, STORES ST, Customers C

    WHERE 

    A.STOREID = ST.STOREID

    AND 

    A.STOREID = OC.STOREID

    AND 

    A.EODID = OC.EODID

    AND 

    A.TICKETSEQ = OC.TICKETSEQ

    AND 

    A.STOREID = T.STOREID

    AND 

    A.EODID = T.EODID

    AND 

    A.TICKETSEQ = T.TICKETSEQ

    AND 

    A.STOREID = DFS.STOREID

    AND 

    A.EODID = DFS.EODID

    AND 

    A.STOREID = C.STOREID

    AND 

    A.EODID = C.EODID

    AND 

    A.TICKETSEQ = C.TICKETSEQ

    --The date should be YESTERDAY'S DATE!

    AND 

    DFS.DF_BusinessDay = '2012-04-07'

    AND 

    OC."name" like '$$%'

    AND 

    Voided <> 1

    order 

    by 1,3,6

    9. dubna 2012 12:39
  • Hi,

    Manish has created TESTID table only for testing. You should take the definition of the column (you must change word MYREASON to T.Notes)

    SUBSTRING(T.Notes,PATINDEX('%ERROR ADDING COUPON:%',T.Notes),(select PATINDEX('%.%',T.Notes)-PATINDEX('%ERROR ADDING COUPON:%',T.Notes))) AS YourColumn

    and add it to your querry (between words Total and FROM - after word Total you should add the comma). Then, your query will get the column with the information that you need.

    Regards,

    gc

    24. dubna 2012 12:23
  • Thank you Manish for your quick response. I'm not sure where I need to add this code, or do I create a brand new SQL statement?  If I create a new statement, how do I load the current ErrorNotes2 field for testing with the TESTID field?

    Here is my SQL statement to build the report that I previously attached:

    select

    .STOREID as 'Store#',

    ST

    .Name AS 'StoreName',

    DFS

    .DF_BusinessDay AS 'OrderDate',

    c

    .LastName,

    T

    .TICKETID AS 'Ticket#',

    A

    .Name AS 'ItemDescription',

    T

    .Notes AS 'Reason for OrderLink Discount?',

    T

    .Notes AS 'ErrorNotes2',

    PurchaseSubtotal

    ,

    CreditSubtotal

    ,

    ChargeSubtotal

    ,

    TaxSubtotal

    ,

    Total

    FROM

    ALLITEMS A, ORDERCREDITS OC, TICKETS T, DAYFILESUMMARY DFS, STORES ST, Customers CA.STOREID = ST.STOREID

    AND 

    A.STOREID = OC.STOREID

    AND 

    A.EODID = OC.EODID

    AND 

    A.TICKETSEQ = OC.TICKETSEQ

    AND 

    A.STOREID = T.STOREID

    AND 

    A.EODID = T.EODID

    AND 

    A.TICKETSEQ = T.TICKETSEQ

    AND 

    A.STOREID = DFS.STOREID

    AND 

    A.EODID = DFS.EODID

    AND 

    A.STOREID = C.STOREID

    AND 

    A.EODID = C.EODID

    AND 

    A.TICKETSEQ = C.TICKETSEQ

    --The date should be YESTERDAY'S DATE!

    AND 

    DFS.DF_BusinessDay = '2012-04-07'

    AND 

    OC."name" like '$$%'

    AND 

    Voided <> 1

    order 

    by 1,3,6

    WHERE 

    ST

       Agreed with Koles reply,  I just created a table called TESTID and having the column MYREASON.

       Try

       

    select
    ST
    .STOREID as 'Store#', 
    ST
    .Name AS 'StoreName', 
    DFS
    .DF_BusinessDay AS 'OrderDate', c
    .LastName, 
    T
    .TICKETID AS 'Ticket#', 
    A
    .Name AS 'ItemDescription', 
    T
    .Notes AS 'Reason for OrderLink Discount?', 
    SUBSTRING(T.Notes ,PATINDEX('%ERROR ADDING COUPON:%',T.Notes ),(select PATINDEX('%.%',T.Notes )-PATINDEX('%ERROR ADDING COUPON:%',T.Notes ))) AS 'ErrorNotes2', 
    PurchaseSubtotal
    , 
    CreditSubtotal
    , 
    ChargeSubtotal
    , 
    TaxSubtotal
    , 
    Total
    FROM
    ALLITEMS A, ORDERCREDITS OC, TICKETS T, DAYFILESUMMARY DFS, STORES ST, Customers C
    WHERE 
    A.STOREID = ST.STOREID 
    AND 
    A.STOREID = OC.STOREID 
    AND 
    A.EODID = OC.EODID 
    AND 
    A.TICKETSEQ = OC.TICKETSEQ 
    AND 
    A.STOREID = T.STOREID 
    AND 
    A.EODID = T.EODID 
    AND 
    A.TICKETSEQ = T.TICKETSEQ 
    AND 
    A.STOREID = DFS.STOREID 
    AND 
    A.EODID = DFS.EODID 
    AND 
    A.STOREID = C.STOREID 
    AND 
    A.EODID = C.EODID 
    AND 
    A.TICKETSEQ = C.TICKETSEQ 
    --The date should be YESTERDAY'S DATE!
    AND 
    DFS.DF_BusinessDay = '2012-04-07' AND 
    OC."name" like '$$%' 
    AND 
    Voided <> 1 
    order 
    by 1,3,6 


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Navržen jako odpověď koles 25. dubna 2012 6:00
    24. dubna 2012 12:51