If you get a yen for something a bit wider you might be out of luck

by Michael S. Kaplan, published on 2007/11/09 10:16 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2007/11/09/6006868.aspx

It was just yesterday that former colleague from way back in the 20th century Scott McMaster asked me a question via email.

He and I got back in touch recently via LinkedIn (which I think we both use in a passive mode1).

Anyway, his question:

Why doesn’t .NET parse full-width Yen as a valid currency symbol in the ja-JP culture?  I’m seeing it in some data files, and it’s really screwing me up.  I'm not really excited about my idea of blindly replacing it with half-width.  I read your related blog entries on the subject, and I didn’t see anything there (although it sounds like SQL/S actually does respect FFE5).

Here is some sample code:

 

            CultureInfo culture = CultureInfo.GetCultureInfo("ja-JP");

            string yen = "¥";  // U+00A5

            string fullWidthYen = "￥";    // U+FFE5

 

            bool halfWidthIsDefault = culture.NumberFormat.CurrencySymbol == yen;

 

            // OK.

            Decimal value = Decimal.Parse(yen + "5",

                NumberStyles.AllowThousands | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint,

                culture);

            Console.WriteLine(value);

 

            // KABLOOEY!

            value = Decimal.Parse(fullWidthYen + "5",

                NumberStyles.AllowThousands | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint,

                culture);

            Console.WriteLine(value);

 

Any insight would be greatly appreciated.  Thanks!

 

--scott

Much of the answer to this question can be found buried in the .NET piece of SQL Server and the CLR have different ways of getting the money, with perhaps a bit lore info in Show me the [small]money! and I'd rather call it the path separator, as well.

Although most of the post was criticizing the SQL Server behavior, in truth I find the .NET support (as vaguely described in the AllowCurrencySymbol or Currency NumberStyles enumeration topic) to be even more disappointing, especially as it fails to extend to numbers and number-like things as discussed in That's funny, they look like digits!. Though perhaps SQL Server is just as "broken" in that case, I haven't really tried it but I suspect that it does not work....

SQL Server is this case has started in SQL Server 2000 and continued in SQL Server 2005 with an incomplete (and thus ultimately somewhat inadequate) attempt to capture every possible currency symbol in order to know what to ignore in insertion of text to its money and smallmoney columns. Functionality that makes sense until you think about how it is incomplete in even what it tries to do.

By the way, if your name is either Brandon or Goldie and you are reading this, could you make sure a bug gets put in to get the list updated as discussed here? If you are someone else on the SQL Server team, feel free to send them over here. :-)

Beyond that, the feature is generally broken since it encourages people to make currency conversion mistakes.

And .NET has gone in the other direction and is only willing to handle a single currency symbol and format as defined in a NumberFormatInfo instance, and is entire unwilling to accept either:

But doesn't this seem like the sort of problem that could have been ideally solved via a Parse/ParseExact distinction, rather than always forcing such a locked down parse operation?

And imagine what this does for the SQLCLR side of things here -- wouldn't everyone be much happier if this was not yet another difference between SQL Server and the CLR when it is hosted within SQL Server?

I think a more integrated SQLCLR experience that felt less like a "bolt-on" might make people more willing to accept the feature, a topic that deserves its own blog post to try to get my full list of things missing from SQLCLR down in one place....

 

1 - By LinkedIn passive mode, I mean we both mostly just wait until someone adds us, and then we go in and look at their contact list to see if there is someone we ought to add from their contacts list. This keeps it from feeling like an MLM/Amway kind of thing.:-)

 

This post brought to you by ￥ (U+ffe5, a.k.a. FULLWIDTH YEN SIGN)

JM on 9 Nov 2007 2:16 PM:

OK, going off on a tangent here, but the whole "CLR in SQL Server" is just wrong. I say this as a member of the old school that believes that the database is the database and the application is the application and never the twain should meet through anything but SQL, so take that as an admission of bigotry if you want.

Still, the SQL Server team appears to agree with this philosophy, as evidenced by the many restrictions and caveats of CLR code hosted in SQL Server ("OK, .NET code, you can come in, but stay over there in the corner and keep quiet, papa RDBMS has work to do"). The whole SQLCLR affair is defined by what you *cannot* do far more than it is defined by what you *can* do, and in what you can do it's much more defined by what *isn't* easy to do.

At present, you are still far better off keeping your SQL Server code restricted to T-SQL, and using the full power of the CLR on the client side. That way the different philosophies can do their work without getting in each other's way, and you can let other people spill their blood on the cutting edge. CLR in SQL has great potential for when it's a first-class citizen, but it's just not there yet.

Michael S. Kaplan on 9 Nov 2007 2:37 PM:

I would argue that better interop and more consistent behavior on the CLIENT side would also be desirable here -- so even if you don't like SQLCLR the pure SQLS server an pure .NET client still suffers from incompatibility issues t present (I'll be blogging more about this soon).

Please consider a donation to keep this archive running, maintained and free of advertising.
Donate €20 or more to receive an offline copy of the whole archive including all images.

go to newer or older post, or back to index or month or day