Transact-SQL - Joel Lipman .Com

T-SQL functions to convert Strings to Tables

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

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

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

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

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

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

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

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

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

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

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

T-SQL functions to convert Strings to Tables

Convert to Proper Case in T-SQL

T-SQL: Parse an XML value

Compare two databases using T-SQL

Split a row into multiple rows based on a column value

T-SQL concatenate an incremental row number

T-SQL Conversion failed when converting the varchar to data type int

CharIndex Reverse - find occurrence starting from end of string in TSQL

DataScramble - Randomizing data rows

DataTumble - Randomize Data Rows

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support