ASP

The crew at 4 Guys From Rolla have come through for me again (as they frequently do in matters of ASP, .NET and SQL). In building a new webservice, I wanted to be able to pass in a comma-delimited list of ID's to a SQL stored procedure. Unfortunately, there isn't a SQL datatype for something like "comma delimited list". I could have taken the "easy" way out and built the SQL query dynamically in C# instead of calling a stored procedure, but then I would have been mad at myself for breaking the rule that we agreed on that all the webservices would strictly call stored procedures. I figured I'd have to use SQL to parse a passed in string into ID's, and build the SQL query from there. However, this seemed like something someone else probably would have done in the past. I found a few techniques online but this one seemed the cleanest and best-implemented. You basically pass in a big varchar string of id's into the stored procedure and then call a SQL User Defined Function called "Split" which returns a table of the ID's which you can join to. It's simpler than it sounds...instead of having something like

SELECT *
FROM table
WHERE id in (--lots of convoluted code to parse out the ids--)

you just call

SELECT *
FROM table
WHERE id in ( SELECT convert(int,Value) FROM dbo.Split(@list_string,',') )

The complete code for the function and examples of how to use it are listed in the article. Thanks to "4 Guys" for saving me a couple hours and some headaches with this nifty technique!

The Setup

You have a double formatted as a string with the decimal part separated from the whole part by a full-stop, e.g. "1234.9". Of course, not all cultures separate their digits the same way, and in fact, the site you're working on has a locale setting for a locale that uses a comma to separate the integer from the decimal, and vice-versa, e.g. German:"1.234,9" or French "1 234,9" style digit grouping. For the sake of argument, let's also say the decimal number is being parsed out of an XML file input by a US or UK user and that a full-stop is meant to separate the whole and fractional parts of the number.

The Problem

ASP/VBScript's clever and useful FormatNumber and FormatCurrency functions which solve oh-so-many related problems are no help:

SetLocale("de-DE")
'This is what we want (notice no quotes below)
FormatNumber(1234.9) '-> 1.234,90

'This is what we get (when using a string)
FormatNumber("1234.9") '-> 12.349,00
FormatCurrency("1234.9") ' -> 12.349,00 €
'ARGH!

When in a German locale, FormantNumber sees the string "1234.9", it assumes that the full-stop is merely a misplaced thousands separator and drops it. Though I was confused and angry at this behavior at first, I have come to see the sense that it makes (consider interpreting "1.234" in a German locale).

I have a love-hate relationship with the "SetLocale" function in ASP. A recent challenge was to build several international versions of a website. We wanted it have one code-base with different settings and translation files. ASP's "SetLocale" function was invaluable in getting this working correctly, but here are a few things you may want to pay attention to in a similar situation, especially in regard to setting currency display :

* Currently there is no "Euros" locale. With a Windows XP 2003 server, the only locale setting that shows Euro format for currency is "es-es" (which is Spain). So, if you want your German site to show currency as Euros using ASP's "FormatCurrency" function, you have to set the locale to Spain. It works, but doesn't seem ideal. Perhaps have a setting for "Currency-Locale" which you set before displaying any currency on your site, and then switch back to the original locale once you display?

* Notice above I said, "Windows XP 2003" as the OS...If you happen to have an older OS (in our case, one of our TEST servers is an older OS), your road is more difficult...you just have to trust that Euros will work on a properly updated server...either that or the headaches of trying to write your own CurrencyFormat function.

As long as you are aware of the above issues, you should be all set. A great summary I found for this issue is at this site.