T-SQL Replace Every Occurrence of Specific Text in a Column with Other Text

This syntax can be used to replace any instance of a word or string of some specific text that appears anywhere in a column (in any row) with some other text. 

To make a global change to column "Comment":

update MyTable set Comment = replace (Comment ,'all instances','any instance') 

This selective edit came in very handy the other day.

remove entry from run command

Open up regedit.exe through the start menu run box, and then navigate down to the following key: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\RunMRU

Reference for Asci for special symbols

Found this excellent reference http://amp-what.com  . Gives the asci for special chars with a very nice visual display. 

Enter the request in search field: triangle, arrow, etc.

For example, use asci &#9656 for the following small right pointing arrow: ▸

Why use an image when a character will do?

I came across some very cool t-sql to generate a comma delimited list of (char,varchar) column values from selected rows:  (Note: the '' in the code snippets are two single quotes ' ', not a double quote)

DECLARE @MyList VARCHAR(1000)
SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles
SELECT @MyList


For a comma delimited list of integer values, use:

DECLARE @MyList VARCHAR(1000)
SELECT @MyList = ISNULL(@MyList,'') + CAST(Id AS CHAR(5)) + ', ' FROM MyPeople WHERE firstname=''  AND  lastname= ''
SELECT @MyList


I find this very useful for generating a list to be used in a t-sql  WHERE clause, for example:
Let's say the above query generated the result  :   2,4,6,25,66,
1. I copy the string  2,4,6,25,66,
2. Then remove the last comma to get 2,4,6,25,66
3. and paste the edited result into the following example query:
UPDATE MyPeople SET  col_note= 'Bad Data' where Id in (2,4,6,25,66)

To see this code in action, use the following script:

CREATE TABLE Titles(
PKey int NOT NULL,
Title varchar(50) NULL
)

insert Titles ( pkey, title) values (1, 'Doctor')
insert Titles ( pkey, title) values (2, 'Nurse')
insert Titles ( pkey, title) values (3, 'Administrator')
insert Titles ( pkey, title) values (4, 'CMA')

select * from Titles

DECLARE @MyList VARCHAR(1000)
SET @MyList = ''
SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles
SELECT @MyList DECLARE @MyList VARCHAR(1000) SELECT @MyList = ISNULL(@MyList,'') + cast(contacts_PKey as char(5)) + ', ' FROM MedicalPersons where firstname='' and lastname= '' select @MyList

Using REGEX to Change Case Selectively

I had a list of generic medications where the names were capitalized. I needed to change the spelling to lower case, which is the accepted way to spell them.

However, converting the medication name ToLower was too simplistic, since certain capitals needed to be preserved:

abobotulinumtoxinA
alteplase, tPA
aspirin, ASA
avian influenza A (H5N1) virus vaccine

Here is the code I used:

private string ConvertStringToLC(string name)
{

foreach (Match m in Regex.Matches(name, @"[A-Z]{1}[a-z]+"))
{
name = name.Substring(0, m.Index ) + char.ToLower(name[m.Index]) + name.Substring(m.Index + 1);
}

return name;
}

Because I was updating a sql database column I also had to take care of strings that had an apostrophe in them, such as burow's solution.

drugNameNew = drugNameNew.Replace("'", "''");

One last thing. If the generic drug name is used at the start of sentence it needs to be capitalized.

drugNameGeneric = char.ToUpper(drugNameGeneric[0]) + drugNameGeneri.Substring(1);

How to Convert an Image in jpg or other Format to an Icon

To convert an image in jpg or other format to an Icon

Open the image in Ms Paint

Click on File -> Save As and

1. Choose “Save as Type” as 24-bit Bitmap(*.bmp)
and
2. name the file with an extention of .ico: TheFileName.ico

The image does not need to be resized (to 32 x 32 or other icon size)

Two C# Solutions for the FizzBuzz Problem

In case you are interested...

namespace FizzBuzz
{
    class Program
    {
        static void Main(string[] args)
        {
            const string fizz = "FIZZ";
            const string buzz = "BUZZ";
            const string fizzbuzz = "FIZZBUZZ";

            // I did this solution second
            for (int i = 1; i < 101; i++)
            {
                string output;
                if (i / 15 * 15 == i) output = fizzbuzz;
                else if (i / 3 * 3 == i) output = fizz;
                else if (i / 5 * 5 == i) output = buzz;
                else output = i.ToString();
                Console.WriteLine(output);
            }

            // and this solution third 
            for (int i = 1; i < 101; i++)
            {
                string output = (i / 15 * 15 == i) ? fizzbuzz : (i / 3 * 3 == i)
                    ? fizz : (i / 5 * 5 == i) ? buzz : i.ToString();
                Console.WriteLine(output);
            }
   
            Console.ReadLine();
        }
    }
}
// Trust me, you don't want to see the one I did first.