ASP.NET validate values input with comma

When user input integer or float values, sometimes they will include comma in the value. For example, instead of inputting "123456", they might input "123,456". However, the Asp.net comparevalidator will not recognize comma and will give user an error message. To fix it, I use a Regulare Expression validator. Here is the regular expression:


"(-)*[0-9]+(,[0-9]+)*(.[0-9]+)*"

And the ASP.NET tags:
<asp:TextBox runat="server" ID="txtValue"></asp:TextBox>

<asp:RegularExpressionValidator runat="server" ID="RegularExpressionValidator1" ErrorMessage="Value has to be number." Display="Dynamic" ForeColor="Red" ControlToValidate="txtValue" ValidationExpression="(-)*[0-9]+(,[0-9]+)*(.[0-9]+)*"></asp:RegularExpressionValidator>

Here is the compare validator which will only work with int/float values without comma
<asp:CompareValidator runat="server" ID="cvValue" ErrorMessage="Value has to be number." Display="Dynamic" ForeColor="Red" ControlToValidate="txtValue" Operator="DataTypeCheck" Type="Double"></asp:CompareValidator>


Happy programming~

SQL Server: format values with comma

I need to format values in SQL server with comma separated. For example, value "123456" should be "123,456" and "12345.1234" should be "123,45.1234". Unfortunately, I couldn't find any build in function in SQL server to achieve this. I was thinking to write my own function to format this kind of value, then I found out that SQL server can format "money" type value this way.

DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  --1322323.67    
Rounded but no formatting

SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67    
Formatted with commas

SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666 
No formatting

The only issue is this function will automatically format the decimal points also. For example, the above one should be " 1,322,323.6666" instead of "1,322,323.67" in my requirement. 
So I decided to write my own function to format the values by taking advantage of the convert function for money. My basic algorithm is 
1) divide value into "integer" part and "decimal" part
2) Converting the "integer" part to money type first and then format it by using "convert(varchar, @v, 2)" function
3) Combine the formatted "integer" part with the original "decimal" part.


Here is the source code of my function:


create FUNCTION [dbo].[FormatValueWithComma]
(
    -- Add the parameters for the function here
    @v nvarchar(30)
)
RETURNS nvarchar(30)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result nvarchar(30)

    -- Add the T-SQL statements to compute the return value here
   
   
    declare @dotIndex as int
    set @dotIndex = charindex('.', @v)   
    declare @number as varchar(30) -- the integer part before decimal point

    if @dotIndex = 0
    begin
       -- convert to money and then convert to varchar
       set @number = convert(varchar, cast(@v as money), 1)      
        set @Result = substring(@number, 0, len(@number)-2)
    End
    else
    begin
        set @number = convert(varchar, cast(substring(@v, 0, @dotIndex) as money), 1)
        set @Result = substring(@number, 0, len(@number)-2)+  substring(@v, @dotIndex, len(@v))
    end


    -- Return the result of the function
    RETURN @Result

END


Happy programming~