SQL Server Requires Chilkat v11.7.0+

Search a PDF with a Regular Expression

See more PDF Text Extraction Examples

Demonstrates searching a PDF with a regular expression (regex: true), limiting the number of results (maxResults), and matching whole words only (wholeWord). SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  The PDF used in this example is available at order_records.pdf

    DECLARE @pdf int
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/order_records.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @jsonOut int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT

    --  Find every order number.  With regex set to true, the query is a regular expression.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'query', 'ORD-[0-9]{4}-[0-9]{4}'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'regex', 1
    --  We only want the matched text, so no context is needed.
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'context', 0

    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END


    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Order numbers found: ' + @iTmp0
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_i OUT, 'matches'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonOut, 'I', @i
        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'matches[i].text'
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    --  Find the e-mail addresses, but stop after the first three.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'query', '[a-z0-9._]+@[a-z]+[.][a-z]+'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'maxResults', 3
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END


    PRINT 'First three e-mail addresses:'
    SELECT @i = 0
    EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_i OUT, 'matches'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonOut, 'I', @i
        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'matches[i].text'
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    --  wholeWord: true matches only whole words.  The word "cat" appears once in the PDF,
    --  but it is also inside "catalog" and "category".
    EXEC sp_OAMethod @json, 'Delete', @success OUT, 'maxResults'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'regex', 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'query', 'cat'
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Occurrences of cat (any): ' + @iTmp0

    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'wholeWord', 1
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'search', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Occurrences of cat (whole word): ' + @iTmp0

    --  Sample output:

    --  Order numbers found: 9
    --  ORD-2026-0301
    --  ORD-2026-0302
    --  ORD-2026-0303
    --  ORD-2026-0304
    --  ORD-2026-0305
    --  ORD-2026-0306
    --  ORD-2026-0307
    --  ORD-2026-0308
    --  ORD-2026-0307
    --  First three e-mail addresses:
    --  alice.moore@example.com
    --  bkumar@example.org
    --  carla@example.net
    --  Occurrences of cat (any): 3
    --  Occurrences of cat (whole word): 1

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonOut


END
GO