So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles and departments.

We wanted a T-SQL version despite having successfully built a custom script component for SSIS.

You can search my site for how to do this in VB or C#. This version is entirely using T-SQL and we're going to use a function so no dynamic SQL (execute, evaluate).

This took me a while to find so I've posted an article below detailing how to parse or extract values from a string containing XML code.

I'm working with a system which stores XML strings in a database and rather than a separate file, it stores these in a row.


Applies to:
  • MS SQL Server Management Studio 2008
  • Two Transact-SQL databases
A quick article on how to compare two Microsoft databases using the tools provided with SQL Server and without having to download any third-party products.

I googled and binged and all I could find were people selling third party products... they don't get it. If you are reading this, it's likely you've paid for a commercial version of a Microsoft product which cannot possibly be cheap. MS SQL Server Management Studio (SSMS) comes included so use it...

Well I started with the method #1 listed here and then just built on this.

Applies to
  • Transact-SQL (T-SQL)
This is a quick article on how to split a single row into multipe rows based on the value of a column in the same table.

I have a table that has all the days of sickness of employees. This table contains, which employee, on what date, and for how many days they were off sick. When migrating to a new system, the destination wanted 1 row per day. This meant that if in the old system, there was 1 row with an employee who took 2 days off, we would want 2 rows for that date for the same employee in the new system.

What we have:
        , DaysOffSick
        , DateOfSickness
FROM    Employees_Attendance_Table

-- yields

        EmployeeNo      DaysOffSick     DateOfSickness
        --------------  --------------  ----------------
        001             1.00            1997-11-17
        002             3.00            2000-02-18
        003             2.00            1999-02-25
  1.  SELECT 
  2.          EmployeeNo 
  3.          , DaysOffSick 
  4.          , DateOfSickness 
  5.  FROM    Employees_Attendance_Table 
  8.  -- yields 
  10.          EmployeeNo      DaysOffSick     DateOfSickness 
  11.          --------------  --------------  ---------------- 
  12.          001             1.00            1997-11-17 
  13.          002             3.00            2000-02-18 
  14.          003             2.00            1999-02-25 
What we want:
EmployeeNo      DaysOffSick     DateOfSickness
--------------  --------------  ----------------
001             1.00            1997-11-17
002             1.00            2000-02-18
002             1.00            2000-02-19
002             1.00            2000-02-20
003             1.00            1999-02-25
003             1.00            1999-02-26

-- note the dates increment and do not account for days off (eg. Saturday / Sunday)
  1.  EmployeeNo      DaysOffSick     DateOfSickness 
  2.  --------------  --------------  ---------------- 
  3.  001             1.00            1997-11-17 
  4.  002             1.00            2000-02-18 
  5.  002             1.00            2000-02-19 
  6.  002             1.00            2000-02-20 
  7.  003             1.00            1999-02-25 
  8.  003             1.00            1999-02-26 
  10.  -- note the dates increment and do not account for days off (eg. Saturday / Sunday) 

Category: Transact-SQL :: Article: 519

This is a quick note on finding the last occurrence of a string in a longer string. This has to be in Transact SQL for a SQL Server instance only and not filtered by other code.

I have a string such as the following (column positions added for demo purposes):
1   5   10   15   20   25   30 -> length = 31
  1.  String1.String2.String3.String4 
  2.  1   5   10   15   20   25   30 -> length = 31 
I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form.

For argument's sake, I'm assigning this string to the variable "haystack".

Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle):
Category: Transact-SQL :: Article: 478

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map

Joes Word Cloud

where   windows   find   work   source   parameter   error   could   code   value   used   need   version   table   data   would   solution   user   joomla   system   using   files   display   database   create   uploaded   deluge   time   mysql   following   report   page   google   date   website   function   order   form   license   first   creator   name   note   file   zoho   added   script   server   list   field   JoelLipman.Com


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2023 Joel Lipman .com. All Rights Reserved.