What?
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.

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

How?
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).

What?
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.

Why?
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.

How?

Applies to:
  • MS SQL Server Management Studio 2008
  • Two Transact-SQL databases
What?
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.

Why?
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...

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


Applies to
  • Transact-SQL (T-SQL)
What?
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.

Why?
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:
copyraw
SELECT
        EmployeeNo
        , 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 
  6.   
  7.   
  8.  -- yields 
  9.   
  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:
copyraw
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 
  9.   
  10.  -- note the dates increment and do not account for days off (eg. Saturday / Sunday) 

How?
Category: Transact-SQL :: Article: 519

What?
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.


Why?
I have a string such as the following (column positions added for demo purposes):
copyraw
String1.String2.String3.String4
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".


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

What?
Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to suppliers/developers, this is a function which simply finds random characters and inserts these.

I would recommend using the DataTumble script over this one as this leaves data very difficult to work with:
copyraw
Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           PDUHjRWJcb           1926-01-02
           2           WRmNqQKxvuV          1969-03-14
           3           nBCkAVDrvdhe         1968-05-05
           4           RJDsFMaeNcLrcMWw     1964-08-08
  1.  Before: 
  2.             StudentID   StudentName          DateOfBirth 
  3.             ----------- -------------------- ------------- 
  4.             1           John Smith           1990-03-21 
  5.             2           Fred Bloggs          1988-11-02 
  6.             3           Another User         1985-07-11 
  7.             4           Yet Another User     1977-06-25 
  8.   
  9.         After: 
  10.             StudentID   StudentName          DateOfBirth 
  11.             ----------- -------------------- ------------- 
  12.             1           PDUHjRWJcb           1926-01-02 
  13.             2           WRmNqQKxvuV          1969-03-14 
  14.             3           nBCkAVDrvdhe         1968-05-05 
  15.             4           RJDsFMaeNcLrcMWw     1964-08-08 


How?
Category: Transact-SQL :: Article: 460

What?
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:

Before:
copyraw
ID        Name         DateOfBirth
--------- ------------ -----------
1         John Smith   1988-06-24
2         Fred Bloggs  1972-11-17
3         Another User 1964-02-18
  1.  ID        Name         DateOfBirth 
  2.  --------- ------------ ----------- 
  3.  1         John Smith   1988-06-24 
  4.  2         Fred Bloggs  1972-11-17 
  5.  3         Another User 1964-02-18 
After:
copyraw
ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17
  1.  ID        Name         DateOfBirth 
  2.  --------- ------------ ----------- 
  3.  1         Fred Bloggs  1964-02-18 
  4.  2         Another User 1988-06-24 
  5.  3         John Smith   1972-11-17 
Looks pretty good, doesn't it? The advantages of this is that you can send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble).

How?
Category: Transact-SQL :: Article: 459

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

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

Accreditation

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:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.