Roman Numerals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Roman Numerals

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-07 : 09:22:16

Jim writes "SELECT (SELECT COUNT(*) FROM customer B WHERE
b.cu_NAME + b.cu_acct_num < a.cu_NAME + a.cu_acct_num)+ 1 AS 'SEQUENCE',
cu_NAME,cu_acct_num
FROM customer A
ORDER BY cu_NAME,cu_acct_num

In the above query how can I display the SEQUENCE in roman numerals? Assume there will be less than 3999 customers.

Jim."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-07 : 10:03:37

You'll have to write a function to convert an int to a roman numeral. Maybe that was obvious ...

EDIT: hum...this smells like a good readers challenge...I think everybody had to write this in C for programming 101, but is there a good set based method? ... hum

Jay White
{0}

Edited by - Page47 on 11/07/2002 10:09:20

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-11-07 : 10:17:19

On SQL2000 here's a function from Steve Kass


create function Roman (
  @arabic int
) returns varchar(30) as begin
  if @arabic > 10000 return '*'
  declare @roman varchar(30)
  set @roman = replicate('M',@arabic/1000)
  set @arabic = @arabic%1000
  set @roman = @roman + replicate('C',@arabic/100)
  set @arabic = @arabic%100
  set @roman = @roman + replicate('X',@arabic/10)
  set @arabic = @arabic%10
  set @roman = @roman + replicate('I',@arabic)

  set @roman = replace(@roman,replicate('C',9),'CM')
  set @roman = replace(@roman,replicate('X',9),'XC')
  set @roman = replace(@roman,replicate('I',9),'IX')

  set @roman = replace(@roman,replicate('C',5),'D')
  set @roman = replace(@roman,replicate('X',5),'L')
  set @roman = replace(@roman,replicate('I',5),'V')

  set @roman = replace(@roman,replicate('C',4),'CD')
  set @roman = replace(@roman,replicate('X',4),'XL')
  set @roman = replace(@roman,replicate('I',4),'IV')

  return @roman
end
go

HTH
Jasper Smith

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-07 : 10:22:18

That's quite inventive. I was thinking a recursive function myself, but was struggling with the CM/XC/IX bit.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-07 : 10:29:34

Hmm am I really stupid or is there an error in that function???

select dbo.Roman(9999) = MMMMMMMMMCMXCIII

According to me MMMMMMMMMCMXCIII = 9993

IMHO dbo.Roman(9999) should be MMMMMMMMMCMXCIX

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-07 : 10:42:38

quote:

Hmm am I really stupid or is there an error in that function???

select dbo.Roman(9999) = MMMMMMMMMCMXCIII

According to me MMMMMMMMMCMXCIII = 9993

IMHO dbo.Roman(9999) should be MMMMMMMMMCMXCIX

yeah, the @roman should be larger than varchar(30) ... it's a truncation issue.

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-07 : 10:44:34

Yeah I noticed that myself just now by running it in parts. 40 does the trick :)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-07 : 11:39:05

BWAHAHA...


alter proc usp_int_to_rm
    @i int
as
set nocount on
select
    replicate('M',@i/1000) +
    replace(replace(replace(replicate('C',@i%1000/100),replicate('C',9),'CM'),replicate('C',5),'D'),replicate('C',4),'CD') +
    replace(replace(replace(replicate('X',@i%100/10),replicate('X',9),'XC'),replicate('X',5),'L'),replicate('X',4),'XL') +
    replace(replace(replace(replicate('I',@i%10),replicate('I',9),'IX'),replicate('I',5),'V'),replicate('I',4),'IV')
go

alter proc usp_rm_to_int
    @r varchar(50)
as
set nocount on
select
    (datalength(replace(@r,'CM',''))-datalength(replace(replace(@r,'CM',''),'M',''))) * 1000 +
    (datalength(replace(replace(replace(replace(@r,'XC',''),'CD',replicate('C',4)),'D',replicate('C',5)),'CM',replicate('C',9))) - 
        datalength(replace(replace(replace(replace(replace(@r,'XC',''),'CD',replicate('C',4)),'D',replicate('C',5)),'CM',replicate('C',9)),'C',''))) * 100 +
    (datalength(replace(replace(replace(replace(@r,'IX',''),'XL',replicate('X',4)),'L',replicate('X',5)),'XC',replicate('X',9))) - 
        datalength(replace(replace(replace(replace(replace(@r,'IX',''),'XL',replicate('X',4)),'L',replicate('X',5)),'XC',replicate('X',9)),'X',''))) * 10 +
    (datalength(replace(replace(replace(@r,'IV',replicate('I',4)),'V',replicate('I',5)),'IX',replicate('I',9))) - 
        datalength(replace(replace(replace(replace(@r,'IV',replicate('I',4)),'V',replicate('I',5)),'IX',replicate('I',9)),'I','')))
go

Mark-up SQLTeam posts here
http://www.markitup.com/Forum/SQLTeam.asp

Jay White
{0}

Edited by - Page47 on 11/07/2002 11:41:24

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-08 : 09:34:11

Very Nice Page47.

However, shouldn't the year 4999. Be written as

IV CMXCIX

The IV in front should have a bar above it to represent multiply by 1000, since I don't know how I can write that I have used a space instead.

However since roman numerals are used mostly to show dates unless you're converting a jewish or chinese calendar to roman numerals then you should be ok.

Edited by - ValterBorges on 11/08/2002 09:38:38

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-08 : 09:48:49

Valter, feel free to update and post you improvement