![]() Now finally we can add or subtract ThisDec from the running total using the rule:įor each character starting from the left, Pos > 0 ) SELECT Val, ThisOne, ThisDec, Remaining, Pos FROM RomToDec Remaining ) - 1 AS PosįROM RomToDec LEFT OUTER JOIN CharValues ON ( substr (RomToDec. Remaining ) - 1 ) AS Remaining, LENGTH (RomToDec. RomToDec (Val, ThisOne, ThisDec, Remaining, Pos ) AS ( SELECT 0 AS Val, CAST ( NULL AS varchar2 ( 4000 ) ) AS ThisOne, 0 AS ThisDec, Roman. WITH Roman (Numeral ) AS ( SELECT 'MDCCVIII' AS Numeral FROM dual ) ,ĬharValues (Numeral, VALUE ) AS ( SELECT 'M', 1000 FROM dual UNION ALL SELECT 'D', 500 FROM dual UNION ALL SELECT 'C', 100 FROM dual UNION ALL SELECT 'L', 50 FROM dual UNION ALL SELECT 'X', 10 FROM dual UNION ALL SELECT 'V', 5 FROM dual UNION ALL SELECT 'I', 1 FROM dual ) , From that post, here’s the recursive WITH basic syntax: See my previous post on Recursion with Recursive With. We’ll use a CTE, aka recursive subquery, to chomp the string one character at a time from the right, keeping a running total as we go first decode the character, then add or subtract as appropriate to the running total until we’re out of characters. ![]() This is a lovely place to use Recursive With in SQL. If character’s value is less than the previous one, subtract the value from the running total.If the character’s value is greater than or equal to the previous one, add the value to the running total.Convert the character into the value it represents.The algorithm to convert roman numerals to decimal numbers is straightforward.įor each character, starting from the RIGHT (lowest value Roman numeral): So of course my second thought, right after “Doh!”, was “I bet I can write a SQL statement to do this for me next time.” ![]() Earlier this week I got tangled up doing a Roman Numeral conversion in my head.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |