Extracting data from one column to another

Question

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

 

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.