SQL Regular Expressions - Extracting Income From PayPal IPN Data

Extracting Payment Amounts From PayPal IPN Data Using SQL 

By Strictly-Software

Recently I had to come up with a way on one of my sites, www.fromthestables.com, to find the total amount of income received by certain members from their PayPal subscription information. 

This is all stored in an MS SQL Payments table along with the Member ID, the PayPal IPN data and Payment type e.g P for Payment, C for Cancelled, W for Waiting, E for EOT etc.

I also have a table of Member Subscriptions which stores details of every subscription they have ever had including their Member ID, a custom GUID to identify the subscription as they could have had multiple subscriptions. Also I store their PayPal Subscription ID and details of their Sign Up Date, Cancellation Date and Next Payment Date alongside other relevant information.

If you don't know about PayPal and how to make a payment system using their shopping cart or a custom payment system then you can read up about it on their site here.

However the main thing to know is that when payments are made, recurring subscriptions started or cancelled then PayPal will send your site an Instant Payment Notification (IPN) to a callback page for you to handle.

This IPN data is a long string of text including the payers name, email, address, recurring payment information such as the number of days payments are made, plus any custom values you may need for your site such as a unique identifier (GUID).

This is useful in website systems so that you can save the new member in a database table with an ID before they go off to PayPal. When they return to your site this information can be sent back to your IPN callback page so that you can analyse the IPN data and ensure it's a valid callback attempt by making a handshake with PayPals system. Once you know it's valid information you can link the callback and IPN data string to a member by matching their Member ID with the one you stored earlier in the database.

An example of an IPN string looks like the text below and contains all the information about an IPN. There are many types of IPN strings such as those that handle cancellations, waiting, EOT (End Of Term) etc but the one I am interested in that contains two bits of information I require is the Payment Notification. 

You can see the two pieces of information in the IPN text below as they are highlighted in blue and red. The start of the string contains one part and the other is halfway through the data.

mc_gross=30.00&protection_eligibility=Eligible&address_status=confirmed&payer_id=NW2XBXPZ6C78W&address_street=Some+Street&payment_date=13%3A27%3A02+Jan+17%2C+2019+PST&payment_status=Completed&charset=windows-1252&address_zip=D02TX83&first_name=Paul&mc_fee=1.37&address_country_code=IE&address_name=Paul+Hickey¬ify_version=3.9&subscr_id=I-PLD46W039GB&custom=%7B7CD66296-0664-4A51-9ACD-05FE46821D44%7D&payer_status=verified&business=rob%40somesite.com&address_country=Ireland&address_city=Dublin&verify_sign=AczUU94BLMilZ9uHs3gDJVFDFmnrAhoedso-UI.71KEbRJ9deMwoa8KS&payer_email=paulhickey1234%40hotmail.com&txn_id=91X75169EH810362V&payment_type=instant&last_name=Hickey&address_state=DUBLIN&receiver_email=rob%40somesite.com&payment_fee=&receiver_id=5Z7P6UFG56B3P&txn_type=subscr_payment&item_name=Strictly+Software&mc_currency=GBP&item_number=%7B24C686D0-BBF5-54B6-A1D6-215AP099CD43%7D&residence_country=IE&transaction_subject=Membership&payment_gross=&ipn_track_id=98d91af71234

As you can see the amount I want is in the first part is at the beginning of the string, just after mc_gross e.g mc_gross=30.00.

However due to legacy issues of the system and I also need to connect the users PayPal subscription ID to the one I store in the members subscription table.

This is highlighted further along in the string e.g subscr_id=I-PLD46W039GB.

An example of the CLR User Defined Function I use to allow for Regular Expression Replacements can be seen below. It is connected to a DLL that contains the C# code that runs the regular expression replacing, however that is another topic altogether. You just need to know the format of the parameters for the SQL I show you later on.

Personally I store this function in the MSDB system database so that all my databases on the server can utilise it for regular expression replacements.

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[udf_SQLRegExReplace](@Pattern [nvarchar](500), @MatchString [nvarchar](max), @ReplaceString [nvarchar](2000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [asbl_SQLRegExpr].[UserDefinedFunctions].[RegExReplace]

The SQL code I use for totaling up all amounts for a certain member is below.

I use two embedded SQL UDF Replacement Functions to extract just the monetary amount and remove everything after it. I also use the same function to join the Subcriber ID in my table to the subscr_id value in the string. 

This involves using a sub-query that does the cleaning and replacing and returns the monetary value. Then an outer query uses that value and a SUM function to total all the values up.

SET ANSI_NULLS OFF
SELECT SUM(VAL) as Amount
FROM (
  SELECT CAST(msdb.dbo.udf_SQLRegExReplace('&.+?$',msdb.dbo.udf_SQLRegExReplace('^.*mc_gross=',PayPalDetails,''),'') as money) as val 
  --*
  FROM MEMBERS_PAYMENTS as p with (nolock) 
  JOIN MEMBER_SUBSCRIPTIONS as ms with (nolock) 
   ON ms.MemberFK = p.MemberFK
    AND ms.PaypalSubscriptionID = dbo.udf_SQLRegExReplace('&.+?$',dbo.udf_SQLRegExReplace('^.*subscr_id=',PayPalDetails,''),'')
    AND ms.MemberFK = 10342
  WHERE PaymentStatus = 'P'
 ) as t 
WHERE ISNUMERIC(t.VAL)=1

As you can see the SQL function calls are wrapped in an inner SQL statement so that it just returns payment values and then the outer SQL statement returns the SUM amount for a particular member ID value.

This is a good example of having to use SQL CLR Regular Expressions as well as the use of embedded functions to extract hard to get values from a long complicated string of data.

You could attempt to write the regular expression calls so that only one function call is made for the monetary amount and another one for the subscriber ID. However the IPN strings are not always similar in format and sometimes it is better for performance to break your regular expressions down into multiple replacement calls instead of writing a complicated expression to cover all possible formats.

Try it and see for yourself. See if you can re-write the query with one regular expression per extraction, and then compare the performance of the two queries with SQL Performance Monitor.

By Strictly-Software

© 2019 Stictly-Software

SQL To Find The Latest Modified Database Objects

SQL To Find The Latest Modified Database Objects

By Strictly-Software

Lots of times I want to quickly see which database objects I have modified lately without having to open up specialist programs such as AdeptSQL or Redgate etc.

By using the System Views you can easily find the objects you have recently created or modified.

The sys.objects view is what we use here and we can filter the types of object very easily with the [type] column.

The (main) values for this are:

P = Stored Procedure 
U = User Table (includes non clustered indexes added to it) 
D = Default Value Constraint 
FN = Scalar User Defined Function 
TF = Table User Defined Function 
PK = Primary Key 
UQ = Unique Constraint 
SN = Synonym
V   = View

If you really wanted to, you could search the system tables, default constraints, and other objects such as...

D   = Default Constraint
F   = Foreign Key Constraint
FS = CLR Scalar Function
PC = CLR Stored Procedure
IF  = SQL Inline Table Valued Function
IT  = Internal Table
S    = System Table
SQ = Service Queue
X   = Extended Stored Procedure


This example however looks for the latest modified User Defined Functions (Scalar and Table), and Stored Procedures.

SELECT name, create_date, modify_date, [type]
FROM sys.objects
WHERE [type] IN('P' , 'FN', 'TF')
ORDER BY modify_date DESC

This example looks for the most recent created stored procedures that start with the name usp_net_save

SELECT name, create_date
FROM sys.objects
WHERE [type] = 'P' 
 AND name like 'usp_asp_save%'
ORDER BY create_date DESC

This is a very quick and easy way to find the code in an SQL database that you have either modified or updated.

By Strictly-Software

© 2016 Strictly-Software

Rebuilding a Stored Procedure From System Tables MS SQL

Rebuilding a Stored Procedure From System Tables MS SQL

By Strictly-Software

Quite often I find "corrupted" stored procedures or functions in MS SQL that cannot be opened in the visual editor.

The usual error is "Script failed for StoredProcedure [name of proc] (Microsoft.SqlServer.Smo)"

This can be due to comments in the header of the stored procedure that confuse the IDE or other issues that you may not be aware of.

However if you get this problem you need to rebuild the stored procedure or function ASAP if you want to be able to edit it visually again in the IDE.

The code to do this is pretty simple and uses the sys.syscomments table which holds all the text for user-defined objects. We join on to sys.sysobjects so that we can reference our object by it's name.

When you run this with the output as "Results To Grid" you may only get 1-4+ rows returned and the data isn't formatted usefully for you to just copy and paste and rebuild.

Therefore always ensure you chose "Results To Text" when you run this code.

Make sure to change the stored procedure name from "usp_sql_my_proc" to the name of the function of stored procedure you need to rebuild!

SELECT com.text
FROM sys.syscomments as com
JOIN sys.sysobjects as sys
 ON com.id = sys.id
WHERE sys.name='usp_sql_my_proc'
ORDER BY colid

TSQL UDF to return useful dates

A User Defined Function to return useful dates

I had to come up with some calculations for working out the starting and end weekday for a given date earlier and I wrote this UDF for SQL 2000, 2005, 2008.

It returns a number of useful values including dates and strings (which is why the return value is a varchar).

If you want to know the last working day for the current month, last month or the last weekday for a month then this function will help.

You can pass in the current date e.g GETDATE() or pass in your own datetime value.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

=============================================================================
-- Author: Rob Reid
-- Create date: 05-JUL-2011
-- Description: Returns useful dates for calculations and formatting
/*


-- example usage

DECLARE @dte datetime
SELECT  @dte = GETDATE() --OR pass in a literal date e.g '2011-Jun-03 03:54:00'

SELECT dbo.udf_GET_DATE_OF('FIRST DAY OF LAST MONTH',@dte) as 'First Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF MONTH',@dte) as 'Last Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF WEEK',@dte) as 'Last Day of Week',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Week', 
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF MONTH',@dte) as 'Last Working Day of Month',
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF LAST MONTH',@dte) as 'Last Working Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST WEEKDAY OF MONTH',@dte) as 'First Week Day of Month', 
dbo.udf_GET_DATE_OF('LAST WEEKDAY OF MONTH',@dte) as 'Last Week Day of Month'



*/
=============================================================================


CREATE FUNCTION [dbo].[udf_GET_DATE_OF]
(
@rule varchar(30),
@dte datetime
)
RETURNS VARCHAR(30) AS
BEGIN

DECLARE @ret varchar(30)

IF @rule = 'FIRST DAY OF LAST MONTH'
  BEGIN  
 SELECT @ret = '01/' + UPPER(LEFT(DATENAME(MONTH,DATEADD(MONTH,-1,@dte)),3)) + '/' + CAST(YEAR(  DATEADD(MONTH,-1,@dte)  ) as varchar(4))
  END
ELSE IF @rule = 'FIRST DAY OF WEEK'
BEGIN
 SELECT @ret = DATEADD(dd,-(DATEPART(dw, @dte) - 1),@dte)
END
ELSE IF @rule = 'LAST DAY OF WEEK'
BEGIN
 SELECT @ret = DATEADD(dd,-(DATEPART(dw, @dte) - 7),@dte)
END
ELSE IF @rule = 'FIRST DAY OF MONTH'
BEGIN
 SELECT @ret = DATEADD(dd,-(DAY(@dte)-1),@dte)
END
ELSE IF @rule = 'LAST DAY OF MONTH'
BEGIN
 SELECT @ret = DATEADD(d, -DAY(DATEADD(m,1,@dte)),DATEADD(m,1,@dte))
END
ELSE IF @rule = 'FIRST WEEKDAY OF MONTH'
BEGIN
 SELECT @ret = DATENAME(dw, DATEADD(dd, - DATEPART(dd, @dte) + 1, @dte))
END
ELSE IF @rule = 'LAST WEEKDAY OF MONTH'
BEGIN
 SELECT @dte = DATEADD(dd,-(DAY(@dte)-1),DATEADD(MONTH,1,@dte)),
   @ret = DATENAME(dw,CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
     CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
       WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
       ELSE 0 END
     ), @dte), 113))

       END
ELSE IF @rule = 'LAST WORKING DAY OF LAST MONTH'
BEGIN
   SELECT @ret =  CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
      CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
        WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
        ELSE 0 END
      ), @dte), 113)
END
ELSE IF @rule = 'LAST WORKING DAY OF MONTH'
BEGIN
 SELECT @dte = DATEADD(dd,-(DAY(@dte)-1),DATEADD(MONTH,1,@dte)), 
     @ret =  CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
       CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
         WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
         ELSE 0 END
       ), @dte), 113)
END

RETURN @ret


END

The example usage is given in the UDF definition e.g

DECLARE @dte datetime
SELECT  @dte = GETDATE() --OR pass in a literal date e.g '2011-Jun-03 03:54:00'

SELECT dbo.udf_GET_DATE_OF('FIRST DAY OF LAST MONTH',@dte) as 'First Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF MONTH',@dte) as 'Last Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF WEEK',@dte) as 'Last Day of Week',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Week', 
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF MONTH',@dte) as 'Last Working Day of Month',
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF LAST MONTH',@dte) as 'Last Working Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST WEEKDAY OF MONTH',@dte) as 'First Week Day of Month', 
dbo.udf_GET_DATE_OF('LAST WEEKDAY OF MONTH',@dte) as 'Last Week Day of Month'

I have found this very useful lately when calculating certain statistical reports and maybe some of you will as well.

Find Text Inside a Stored Procedure or Used Defined Function

How to find text inside a stored procedure or user defined function

One of the most useful stored procedures I have in my toolkit that I find myself using over and over on any system that I work on is the following procedure that allows me to return a list of stored procedures and user defined functions that contain a particular string of text.

I maybe looking for all procs or UDF's that contain a table or view name or need a list of all procs that do have SET NOCOUNT ON so that I can find those that don't or I may just be looking for a particular variable name or comment within all my procs and functions.

It makes use of the system view syscomments which stores all the text within the stored procedures and user defined functions contained within your SQL Server database. This is just another example of how having knowledge of the system views is a very useful skill to know.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_find_string_in_proc_or_udf]

@FindStr AS VARCHAR(500)

AS

SET NOCOUNT ON

SELECT DISTINCT NAME AS [NAME],
 CASE  WHEN TYPE ='P' THEN 'PROCEDURE'
     WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
 END AS OBJECTTYPE
FROM SYSCOMMENTS as comm
JOIN sysobjects as obj
ON comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
WHERE lower(TEXT) LIKE '%' + ltrim(rtrim(lower(@FindStr))) + '%'

You call it simply like so:

EXEC dbo.usp_sql_find_string_in_proc_or_udf 'SOME_TABLE'

EXEC dbo.usp_sql_find_string_in_proc_or_udf '@ErrorVar varchar(100)'

It's one of those simple procedures that are very handy to have and save a lot of time. Being able to find a piece of text within all your stored procedures and functions quickly could literally save you hours of hunting about on a large system.

Removing HTML with a User Defined Function

Using SQL to parse HTML content

Today I had the task of collating a long list of items for one of my sites. The list was being obtained from manually checking the source code of numerous sites and copying and pasting the relevant HTML source into a file. The items I wanted were contained within HTML list elements (UL, LI) and therefore the actual textual data was surrounded by HTML styling, tags such as LI, Strong, em with various in-line styles and class names etc.

I didn't want to spend much time parsing the list of a thousand items and I couldn't be bothered to write much code so I reverted back to an old user defined function that I wrote to remove HTML tags. Therefore once I had collated the list it was a simple case of using an import task to insert from the text file into my table wrapping the column in my user defined function.

Although I have been making use of the CLR lately for string parsing in the database with a few good C# regular expression functions this function doesn't require the CLR and can easily be converted for use with SQL 2k and earlier by changing the data type for the input and return parameters from nvarchar(max) to nvarchar(4000).

The code is pretty simple and neat and makes use of a PATINDEX search for the initial bracket in an open or close HTML tag making sure that the subsequent character is either a forward slash to match a closing HTML tag or a letter to match an opening HTML tag e.g

SELECT @StartPos = PATINDEX('<[A-Z/]%', @CleanHTML),

Which means that any unencoded opening bracket characters don't get mistaken as HTML tags. The code just keeps looping the input string looking for opening and closing tags and then recreating the string with the STUFF function until all matches have been found.

-- Look for open and close HTML tags making sure a letter or / follows < ensuring its an opening
-- HTML tag or closing HTML tag and not an unencoded < symbol
WHILE PATINDEX('%<[A-Z/]%', @CleanHTML) > 0 AND CHARINDEX('>', @CleanHTML, CHARINDEX('<', @CleanHTML)) > 0 

SELECT @StartPos = PATINDEX('%<[A-Z/]%', @CleanHTML),
@EndPos = CHARINDEX('>', @CleanHTML, PATINDEX('%<[A-Z/]%', @CleanHTML)),
@Length = (@EndPos - @StartPos) + 1,
@CleanHTML = CASE WHEN @Length>0 THEN stuff(@CleanHTML, @StartPos, @Length, '') END

An example of the functions usage:

DECLARE @Test nvarchar(max)
SELECT @Test = '<span class="outer2" id="o1"><strong>10 is < 20 and 20 is > 10</strong></span>'

SELECT dbo.udf_STRIP_HTML(@Test)

--Returns
10 is < 20 and 20 is > 10

I thought I would post this user defined function on my blog as its a good example of how a simple function using inbuilt system functions such as PATINDEX, CHARINDEX and STUFF alone can solve common day to day problems. I personally love UDF's and the day SQL 2k introduced them was a wonderful occasion almost as good as when England beat Germany 5-1 LOL. No I am not that sad of course England beating Germany was the better occasion but the introduction of user defined functions made SQL server programming a much easier job and made possible numerous pseudo-set based operations which previously had to be done iteratively (loops or cursors).

Download the Strip HTML user defined function source code here