Transact SQL – using xp_sscanf to parse input ( Parse Bible Verse )

 

Background

Let us use xp_sscanf to parse Bible Verse.

Introduction

Bible people will say my hope rests on John 3:16

An inquiry mind might want to look up John 3:16.

In his or her mind, he will break those words down.

In computer jargon, one will tokenize it.

Tokens

Here are the tokens :-

  1. First comes John
  2. Then comes 3
  3. And, next come 16

Verse

For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.
( John 3:16 ) [ Link ]

 

SQL

Let us try to tokenize our passage using Transact SQL.

[dbo].[usp_ParseBibleVerseUsing_sscanf]

Code

Invoke

use [bible]
go

declare
      @verse              varchar(255)
    , @verseBook          varchar(255)
    , @verseChapterNumber varchar(255)
    , @verseVerseNumber   varchar(255)
    , @escape             bit =1
    , @debug              bit =0
    , @rc                 bit

select
    @verse              = 'Genesis 50:1'
    , @verseBook          = null
    , @verseChapterNumber = null
    , @verseVerseNumber   = null
    , @escape             = 1
    , @debug              = 1

exec  @rc = [dbo].[usp_ParseBibleVerseUsing_sscanf]
            @verse = 'Genesis 50:1'
        , @verseBook          = @verseBook output
        , @verseChapterNumber = @verseChapterNumber output
        , @verseVerseNumber   = @verseVerseNumber   output
        , @escape             = @escape
        , @debug              = @debug

select
        [@verse] =  @verse
    , [@rc] = @rc
    , [@verseBook] =  @verseBook
    , [@verseChapterNumber] = @verseChapterNumber
    , [@verseVerseNumber]   = @verseVerseNumber

Output

Output – Image

invoke_Genesis_50_1_20200110_0536PM.PNG

Output – Text


@format :- %s %s:%s (%s %s %s)
@verse :- Genesis 50:1 (Genesis 50:1)
@rc :- 0
@verseBook :- Genesis
@verseChapterNumber :- 50
@verseVerseNumber :- 1

Summary

xp_sscanf is similar to sscanf.

Because of so called special characters, you might have to escape certain control characters.

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s