Created
December 31, 2018 20:48
-
-
Save johnarok/1704c2e39109859b95927e3fa4627fa8 to your computer and use it in GitHub Desktop.
Bible Youversion links sql
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @refs as table | |
| (rdate date, bibleref varchar(50), book varchar(5), chapters varchar(10), startChapter int) | |
| INSERT INTO @refs | |
| (rdate, bibleref, book, chapters) | |
| SELECT rdate, bibleref, case when BibleRef like '%pas%' or bibleref like '%paa%' then UPPER(SUBSTRING(REPLACE(bibleref, 'pas', 'psa'), 1, 3)) | |
| when BibleRef like '1%' OR BibleRef like '2%' then UPPER(REPLACE(SUBSTRING(bibleref, 1, 5), ' ', '')) | |
| else UPPER(SUBSTRING(bibleref, 1, 3)) end, | |
| reverse(SUBSTRING(reverse(bibleref), 1, CHARINDEX(' ', reverse(bibleref)))) | |
| FROM dbo.Reading | |
| WHERE YEAR(rdate) = 2019 | |
| UPDATE @refs | |
| SET startChapter = case when CHARINDEX('-', chapters) = 0 | |
| then chapters | |
| else SUBSTRING(chapters, 1, CHARINDEX('-', chapters)- 1) | |
| end | |
| select | |
| DATE | |
| rdate, bibleref, 'youversion://bible?reference=' + book + '.'+cast(startChapter as varchar) as link | |
| from @refs |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment