Transact-SQL - Joel Lipman .Com

T-SQL functions to convert Strings to Tables

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

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

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

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

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

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

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

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

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

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

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support