Keith Rull

I was really excited last Monday after seeing Expression Studio 4 has been released (I have to admit that I'm one of those developers who logged in at MSDN at 12 midnight to check if the RTM bits are already out. hehe). I think this is great news for all XAMLers all over the world. On thing that I'm really sad about though is that our corporate MSDN license only has Expression Studio 4 Professional and not the Ultimate version. Bummed. So I ended up downloading the 60 day trial up until I can figure out how I can get a copy of the full version (OK, here's the part that I ask donations to get a full copy but I'll leave that out. LOL).

Sad, but still happy. At least I have 60 days to enjoy this new tool. ;)

Last night I was trying to cleanup the spammers from the database of devpinoy.org and while I was evaluating the result sets i was able to conclude that aside from using common spam text like 'cheap', 'buy', 'free', 'deal', 'viagra', 'prozac' that 30% of the false emails that spam accounts are using multiple dots on their email address. A good example is a subset below from the list of offenders that I found in the devpinoy db.


Having found that fact I immediately created a sql script that will delete users from the database if they have more than 2 dots in their email address.

Enough with the side note and here is some code.

DECLARE @string2check varchar(50)
DECLARE @character2find char

SET @string2check = 'this is a very long string'
SET @character2find = 'i'

PRINT LEN(@string2check) - LEN(REPLACE(@string2check, @character2find, ''))

What the code above is doing is that it is removing the characters that matched our search key and then subracts the length of that string to the original string to find the total occurrence of the character we are looking for.

Now, if you want to use this as a function you can use this:

CREATE FUNCTION udf_CountCharOccurence
(    @string2check varchar(500)
 ,    @character2find char
)RETURNS INT
BEGIN

RETURN (LEN(@string2check) - LEN(REPLACE(
                                @string2check, 
                                @character2find)
                                        )
                                )

END
GO

The code above works great but there's a catch. If you are concerned with case sensitivity then the code above wont work. The way around it is to use COLLATION which is supported by the SQL function below:

CREATE FUNCTION udf_CountCharOccurenceCaseSensitive
(    @string2check varchar(500)
 ,    @character2find char
)RETURNS INT
BEGIN

RETURN (LEN(@string2check) - LEN(REPLACE(
                                @string2check COLLATE SQL_Latin1_General_Cp1_CS_AS, 
                                @character2find COLLATE SQL_Latin1_General_Cp1_CS_AS, '')
                                        )
                                )

END
GO

In order to use this in your query all you need to do is

PRINT dbo.udf_CountCharOccurenceCaseSensitive('This is a long text','i')

Or if you want to put it to use to meet the criteria that I mentioned about dots on emails you can do it this way:

SELECT * FROM Users
WHERE dbo.udf_CountCharOccurenceCaseSensitive(EmailAddress,'.') > 2

HTH

Here's a nifty trick using jQuery on how to iterate on all rows of a table except the first row, how to get the value of a column in the current row being iterated and how to change the table cell color depending on the value it contains.

In this case we wanted to change the color of the 3rd column depending on whether it is higher or lower than the second column. There are two examples in here. The first one is select the table via its ID and the second version is selecting the table based on its class name.

 <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script>
 <script type="text/javascript"> 
     $(document).ready(function()
     {    
         //iterate through all the rows in our table called yourtable
         //excluding the first row because those are column titles
         $("#yourtablename tr:not(:first)").each(function() { 
             
             //get the value of the table cell located
             //in the third column of the current row
             var priceYesterday = $(this).find("td:nth-child(2)").html();
             var priceToday = $(this).find("td:nth-child(3)").html();
             
             //check if its greater than zero
             if (priceToday > priceYesterday){
                 //change the color of the text to green if its a positive number
                 $(this).find("td:nth-child(3)").css("color", "#00FF00");
             }
             else if(priceToday < priceYesterday){
                 //change the color of the text to red if its a negatice number
                 $(this).find("td:nth-child(3)").css("color", "#FF0000");
             }
         });
         
         //iterate through all the rows in our table called yourtable
         //excluding the first row because those are column titles
         $(".yourtableclassname tr:not(:first)").each(function() { 
             
             //get the value of the table cell located
             //in the third column of the current row
             var priceYesterday = $(this).find("td:nth-child(2)").html();
             var priceToday = $(this).find("td:nth-child(3)").html();
             
             //check if its greater than zero
             if (priceToday > priceYesterday){
                 //change the color of the text to green if its a positive number
                 $(this).find("td:nth-child(3)").css("color", "#00FF00");
             }
             else if(priceToday < priceYesterday){
                 //change the color of the text to red if its a negatice number
                 $(this).find("td:nth-child(3)").css("color", "#FF0000");
             }
         });

     });
 </script>

Below is the full source for this sample:

Iterating to the table via table id

Product Name	Yesterday	Today
Egg	1.95	2.10
Sugar	1.92	1.88
Milk	1.95	1.97
beans	3.15	3.06

Iterating to the table via class name

Product Name	Yesterday	Today
Egg	1.95	2.10
Sugar	1.92	1.88
Milk	1.95	1.97
beans	3.15	3.06

<html>
    <head>
         <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script>
        <script type="text/javascript"> 
            $(document).ready(function()
            {    
                //iterate through all the rows in our table called yourtable
                //excluding the first row because those are column titles
                $("#yourtablename tr:not(:first)").each(function() { 
                    
                    //get the value of the table cell located
                    //in the third column of the current row
                    var priceYesterday = $(this).find("td:nth-child(2)").html();
                    var priceToday = $(this).find("td:nth-child(3)").html();
                    
                    //check if its greater than zero
                    if (priceToday > priceYesterday){
                        //change the color of the text to green if its a positive number
                        $(this).find("td:nth-child(3)").css("color", "#00FF00");
                    }
                    else if(priceToday < priceYesterday){
                        //change the color of the text to red if its a negatice number
                        $(this).find("td:nth-child(3)").css("color", "#FF0000");
                    }
                });
                
                //iterate through all the rows in our table called yourtable
                //excluding the first row because those are column titles
                $(".yourtableclassname tr:not(:first)").each(function() { 
                    
                    //get the value of the table cell located
                    //in the third column of the current row
                    var priceYesterday = $(this).find("td:nth-child(2)").html();
                    var priceToday = $(this).find("td:nth-child(3)").html();
                    
                    //check if its greater than zero
                    if (priceToday > priceYesterday){
                        //change the color of the text to green if its a positive number
                        $(this).find("td:nth-child(3)").css("color", "#00FF00");
                    }
                    else if(priceToday < priceYesterday){
                        //change the color of the text to red if its a negatice number
                        $(this).find("td:nth-child(3)").css("color", "#FF0000");
                    }
                });

            });
        </script>
    </head>
    <body>
        <h3>Iterating to the table via table id</h3>
        <table id="yourtablename">
            <thead>
                <tr>
                    <td>Product Name</td>
                    <td>Yesterday</td>
                    <td>Today</td>                
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Egg</td>
                    <td>1.95</td>
                    <td>2.10</td>
                </tr>
                <tr>
                    <td>Sugar</td>
                    <td>1.92</td>
                    <td>1.88</td>
                </tr>    
                <tr>
                    <td>Milk</td>
                    <td>1.95</td>
                    <td>1.97</td>
                </tr>
                <tr>
                    <td>beans</td>
                    <td>3.15</td>
                    <td>3.06</td>
                </tr>                
            </tbody>
        </table>
        
        <h3>Iterating to the table via class name</h3>
        <table class="yourtableclassname">
            <thead>
                <tr>
                    <td>Product Name</td>
                    <td>Yesterday</td>
                    <td>Today</td>                
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Egg</td>
                    <td>1.95</td>
                    <td>2.10</td>
                </tr>
                <tr>
                    <td>Sugar</td>
                    <td>1.92</td>
                    <td>1.88</td>
                </tr>    
                <tr>
                    <td>Milk</td>
                    <td>1.95</td>
                    <td>1.97</td>
                </tr>
                <tr>
                    <td>beans</td>
                    <td>3.15</td>
                    <td>3.06</td>
                </tr>                
            </tbody>
        </table>
    </body>
</html>

You can copy and paste the code above and put it on a new file to see it running or you can view this sample running here: jquery_change_table_cell_color_depending_on_value.html (3.05 KB)

HTH