Transact-SQL - Joel Lipman .Com

T-SQL functions to convert Strings to Tables

: Joel Lipman; Transact-SQL; 20 August 2014; Hits: 6506

Applies to:

Microsoft SQL Server 2008 R2
Microsoft SQL Server 2012

What?
These were in a solution and I thought I'd note them on my site so I can refer to them more easily.

How?

: Joel Lipman; Transact-SQL; 04 June 2014; Hits: 25472

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

: Joel Lipman; Transact-SQL; 27 March 2014; Hits: 24230

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?

: Joel Lipman; Transact-SQL; 24 September 2013; Hits: 6790

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.

: Joel Lipman; Transact-SQL; 23 August 2013; Hits: 28539

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:

copy raw

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

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

What we want:

copy raw

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)

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)

How?

Category: Transact-SQL :: Article: 519

: Joel Lipman; Transact-SQL; 24 May 2013; Hits: 9314

What?
This is a quick article on how to use a counter added to the end of a field column of data.

Eh. What?
Assume the following two tables exist:

: Joel Lipman; Transact-SQL; 14 May 2013; Hits: 31374

What?
A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.

How?

: Joel Lipman; Transact-SQL; 28 January 2013; Hits: 39834

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):

copy raw

String1.String2.String3.String4
1   5   10   15   20   25   30 -> length = 31

String1.String2.String3.String4
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

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 7153

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:

copy raw

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

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

How?

Category: Transact-SQL :: Article: 460

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 5196

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:

copy raw

ID        Name         DateOfBirth
--------- ------------ -----------
1         John Smith   1988-06-24
2         Fred Bloggs  1972-11-17
3         Another User 1964-02-18

ID Name DateOfBirth
--------- ------------ -----------
1 John Smith 1988-06-24
2 Fred Bloggs 1972-11-17
3 Another User 1964-02-18

After:

copy raw

ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17

ID Name DateOfBirth
--------- ------------ -----------
1 Fred Bloggs 1964-02-18
2 Another User 1988-06-24
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

: Joel Lipman; Transact-SQL; 26 October 2012; Hits: 7532

What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.

How?

: Joel Lipman; Transact-SQL; 10 October 2012; Hits: 8287

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?