Sam Lester - SQL Server

Language Translation in SQL Server using Bing Translator APIs & SQL CLR

2013-05-04T11:08:36Z

Language Translation within SQL Server using Bing Translator public APIs and CLR Stored Procedures / Functions

Does SQL Server have a built-in way to translate my text data from English to Spanish, German to French, Italian to Russian? The answer is no, SQL Server does not have this functionality built-in, but it is indeed possible to build by leveraging CLR and the public Bing Translator APIs. All C# and SQL Server code has been uploaded as a TechNet Gallery here to make it easier to implement.

I've heard this question a surprising number of times over the past few years and finally had the business need to build it myself. I'm working with a database that is hosted in another country and at one point allowed free text entry into a product description column. While the majority of the descriptions are in English, a somewhat substantial amount were entered in the native language. It is time to clean up this database to arrive at the definitive list of product descriptions, but in order to do so, we need to first translate the native language entries into English. Using the code samples from the TechNet Gallery found here, we can perform language translation as shown below.

Sample call within SQL Server to translate nvarchar text data within a table using the supplied user-defined function:

Sample call within SQL Server showing the flexibility of the stored procedure translating between several different languages:

Since the Bing Translator provides a free public API that can be leveraged by developers, the process to build this application is fairly straightforward. As of April 2013, the process to access these APIs has changed so that you must register for a public access token to obtain your personal "client secret". This is used to obtain a token that is then passed along with the API call to validate the user and supply the requested translation.

The three steps required to obtain the access token are:

Sign up for Bing Translator API: - https://datamarket.azure.com/dataset/bing/microsofttranslator (Scroll to the bottom to find the free registration that allows 2 million characters translated per month)
Register the application: - https://datamarket.azure.com/developer/applications/
Obtaining an Access Token: - http://msdn.microsoft.com/en-us/library/hh454950.aspx

Once you obtain the clientID and clientSecret, you will replace the sample values in the C# application and are ready to compile.

Detailed steps to use the project:

Download and extract the .zipped C# source code and SQL Server found here
Open the C# application, replace ClientID and ClientSecret (lines 69-70) with the values obtained in the steps above, and compile the C# application
From the extracted SQL Server scripts folder, open CreateAssemblyCode.sql within your instance of SQL Server, update the script with the path to your compiled DLL from step #2 and run the script to create the assembly
From the extracted SQL Server scripts folder, open the spCallTranslator and udfCallTranslator for the source code to create the SP and UDF. The SP script also contains a few samples.

Enjoy!
Sam Lester (MSFT)

Bulk Database Migration from On-Premise SQL Server to SQL Azure - Step 1: Bulk .bacpac Export

2012-12-08T17:30:00Z

I recently had a task to perform a bulk migration of hundreds of databases to Windows Azure SQL Database (formerly known as SQL Azure) from an instance of SQL Server 2012. If you need to migrate a very small number of databases, the easiest way is likely through the SSMS export Data-Tier Application wizard. You can manually create a .bacpac file for each of the databases and move them to your blob storage. However, for hundreds of databases, this is not a nice solution. There are several ways to automate this task, but I decided to use TSQL scripting to build up the correct SQLPackage.exe command line call, then use xp_cmdshell to execute. Similarly, the script below can be modified to generate the appropriate command line syntax and be executed outside of SQL Server if needed since most SQL Server environments have xp_cmdshell disabled.

There are several different techniques for migrating an on-premise SQL Server database to SQL Database. My current technical preference is to "export" the database using Data-Tier Application functionality to create a self-contained .bacpac file (NOTE: .bacpac, not .dacpac) that can then be uploaded to blob storage and imported directly into your SQL Database environment. One benefit of using .bacpac export is that the process includes a validation step to verify that all objects in your on-premise database will be supported in SQL Database. Other techniques (scripting, Import/Export wizard, extracting .dacpacs, etc) do not perform this verification during the extraction phase and will result in a failed database creation in the SQL Database environment if you have unsupported objects (ex: a table without a clustered index). These other techniques give different benefits, such as the flexibility to manually modify scripts, etc., but for ease of use, I'm currently sticking with .bacpac export.

Task: Bulk Migration from On-Premise SQL Server to SQL Database - Step 1: Performing Bulk .bacpac Export through TSQL Scripting & SQLPackage.exe

Code Snippet
/*
Purpose:  This script performs bacpac export for all databases in a given server.  If the database contains objects fully supported in 
SQL Database / SQL Azure, then the bacpac export will succeed.  If not, the errors/reasons for failure will be displayed in the result set.
After running the script, save the output to text to help determine what objects need to be modified in order for each DB to be ready to migrate.
 
Author:  Sam Lester (MSFT)
Create Date:  12/1/12
 
Sample Call for bacpac export
sqlpackage.exe /Action:Export /SourceServerName:. /sdn:"DB_Foo" /tf:"c:\MyBacpacs\DB_Foo.bacpac"
*/
 
/* Create table to hold DB names to process */
create table files(files varchar(max))
create table publish(filename varchar(1000), cmd varchar(max))
create table output (output varchar(1000) null)
go
 
declare @filepath varchar(1000), @dircmd varchar(1000), @sqlpackagepath varchar(1000)
  select    @filepath = 'c:\MyBacpacs\'/* path to export .bacpacs --  Create this directory before running */
        ,@sqlpackagepath = 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe'  -- Update this to the correct location for your DacFramework.msi install
 
-- Store the list of databases that we want to export.  If you want a particular subset, add a WHERE clause to filter this list.
insert into files
select name from sys.databases where database_id > 4  --Skip the system DBs
 
/* Build the command line string that we'll later execute in the WHILE loop.  In this example, the instance name is hardcoded 
to the default instance (using . in /ssn:.).  If you have a named instance, update this string to "/ssn:ServerName\InstanceName"  */
insert into publish
  select files, ' /Action:Export /sdn:' + files +' /ssn:. /tf:' + @filepath + files + '.bacpac'
from files
 
DECLARE @counter int, @limit int, @rc int
SET @counter =(select count(*) from publish)
SET @limit = 0
 
-- Loop through and execute each command line to perform the export
WHILE (@limit < @counter)
BEGIN
 
    declare @txt varchar(1000)
    select @txt = 'cmd /c ""' + @sqlpackagepath + '"" ' +(select top 1 cmd from publish) + ' >>   ' + @filepath + 'results_bacpac_export.txt'
 
    insert output select '----------------------------------------'
    insert output select top 1 cmd from publish
    insert output select '----------------------------------------'
    insert output exec @rc = master.dbo.xp_cmdshell@txt
 
    if (ISNULL(@rc,0) = 0)
    begin
        insert output select '--- SUCCESSFUL EXPORT FOR DATABASE = ' +(select top 1 filename from publish)
    end
 
    delete top (1) from publish
    select @limit = @limit + 1
 
END
 
select * from output where output is not null
 
/* Cleanup
 
drop table files
drop table publish
drop table output
*/

The result of running the script is the following:

1. For databases that can be successfully migrated to SQL Database, a .bacpac file is created in the file location supplied in the script. These can then be moved to blob storage and imported.
2. For databases that cannot be successfully migrated to SQL Database, the output of the script provides the list of unsupported objects. Save this output to text to help assess your migration strategy.

Ex:

Hope this helps,
Sam Lester (MSFT)

Standard disclaimer: As with any script/code, please run this in a test server to make sure it works for your specific version/edition/environment settings before tinkering in your production environment.

Creating Complex Test Databases - Generating One Table for each of the 2,397 supported Collations

2012-12-03T01:55:00Z

As a follow up to my series on complex test databases, this article will cover one of the more interesting test databases that I recently created.

Goal: Create a database that includes one table for each supported collation. Each table contains a single column with the various column level collations supported by SQL Server 2012 (nearly 2,400 different collations supported).

The first step here is to determine where we can find the exhaustive list of supported collations. The answer comes from the built-in table-valued function, fn_helpcollations, that returns the list of supported collations in SQL Server 2012. Once we have the exhaustive list of supported collations, we need to determine how we will leverage this list to create one table for each collation. If we were to do this manually, we would write out the following CREATE TABLE statements:

create table T1 (c1 nvarchar(50) collate Albanian_100_BIN)

create table T2 (c1 nvarchar(50) collate Albanian_100_BIN2)

create table T3 (c1 nvarchar(50) collate Albanian_100_CI_AI)

…

create table T2395 (c1 nvarchar(50) collate Yakut_100_CS_AS_KS)

create table T2396 (c1 nvarchar(50) collate Yakut_100_CS_AS_KS_WS)

create table T2397 (c1 nvarchar(50) collate Yakut_100_CS_AS_WS)

The repetition of these statements makes them good candidates for scripting using T-SQL. By leveraging the ROW_NUMBER function as the table numeric identifier, we're able to put together the following statement:

SELECT'create table T'+cast(ROW_NUMBER()over (orderbyname)aschar(10))+'(c1 nvarchar(50) collate '+name+')'

FROM::fn_helpcollations();

The output of this statement is exactly the 2,397 CREATE TABLE scripts needed to create our database. Copy the results and run them in an empty DB to create our desired database.

By leveraging the same language constructs, we could also create different flavors of this same DB.

Example 1: 1 table with 1024 columns of different collations
Example 2: Modify the script to produce one CREATE DATABASE script for each collation

Hope you enjoy,
Sam Lester (MSFT)

TSQL - Solve it YOUR Way - Finding a Person's Current Age Based on Birth Date

2012-12-01T00:56:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic will cover a question asked recently in the T-SQL MSDN forums where the solution, at first, seems extremely obvious. However, as solutions were proposed and flaws were discovered with the proposals, the interesting nature of the problem was revealed. As is the theme of the blog series, different solutions and explanations are provided from some of the most helpful and creative contributors in the TSQL MSDN forums. This week, the contributors are Steven Wang, Olaf Helper, Tom Cooper, and Monica Rivera.

Topic: Determine the age of a person given their birth date.

A fun aspect to this question is the fact that we've all probably made these calculations hundreds of times in our minds throughout our lives as we hear birth dates in conversation so often. The result is that it seems that the calculation should be so simple. However, since SQL Server does not have an AGE() function that takes a datetime parameter and returns an age, we'll need to handle the logic and calculate it ourselves. You can then store the result as a function in your SQL Server DBA/DB Developer toolbox and have the solution forever. Three of the below solutions are somewhat similar using some variation of DATEFIFF(), but vary slightly in interesting ways, while one (Steven Wang's solution) takes on a completely new and interesting approach. This is an outstanding example of why I've enjoyed collaborating with other SQL Server professional on this series to highlight different methods and solutions to a single problem. The solutions show the true power of SQL Server as a programming language and also highlight the freedom that we as DBA's, DB developers, BI experts, etc. have in creating our solutions. I hope you enjoy the solutions as much as I have.

Solution #1 - Provided by Olaf Helper

Code Snippet
DECLARE @reportDate datetime;
SET @reportDate = CONVERT(date,getdate());
 
;WITH
  birthdates AS
   (SELECT 'Peter' AS Name, {d N'1970-09-14'} AS Birthdate UNION ALL
    SELECT 'Bob' AS Name, {d N'1970-11-27'} AS Birthdate UNION ALL
    SELECT 'Sabina' AS Name, {d N'1970-11-28'} AS Birthdate UNION ALL
    SELECT 'Amy' AS Name, {d N'1970-11-29'} AS Birthdate UNION ALL
    SELECT 'Jane' AS Name,  {d N'1970-12-31'} AS Birthdate UNION ALL
    SELECT 'Julie' AS Name, {d N'1971-12-31'} AS Birthdate)
,age AS
   (SELECT *
          ,DATEDIFF(Year, Birthdate, @reportDate) AS Age
          ,DATEADD(Year, DATEDIFF(Year, Birthdate, @reportDate), BirthDate) AS SwitchDate
    FROM birthdates)
 
SELECT age.Name
      ,age.Birthdate
      ,CASE WHEN SwitchDate <= @reportDate
            THEN age
            ELSE age - 1 END AS Age
      ,CASE WHEN SwitchDate = @reportDate
            THEN 'Happy Birthday!!!'
            ELSE '' END AS Gratulation
FROM age
ORDER BY age.Birthdate;

Explanation of Olaf's solution:

The CTE "birthdates" creates sample data around the current date for reporting, plus some additional data to check the result.

The DateDiff with option "YEAR" function seems the first and obvious way to calculate the age, but it returns only the difference of the year part and doesn't account for the additional months.
For example:

SELECT DATEDIFF(YEAR, {d N'2000-01-01'}, {d N'2001-01-01'})
,DATEDIFF(YEAR, {d N'2000-01-01'}, {d N'2001-12-31'})

This returns 1 for both results, even though in the second example, the difference is "nearly" 2 years.

To solve this I still use the DateDiff function, then add the difference between the birth and the given date to the birthdate. In a CASE WHEN statement I compare the result with the given date and if it's higher, I subtract 1 to get the real age.

Solution #2 - Provided by Tom Cooper

Code Snippet
Declare @Test Table(EmpName varchar(40), BirthDate date);
Insert @Test(EmpName, BirthDate) Values
('30 Yrs old yesterday', DateAdd(day, -1, DateAdd(year, -30, GetDate()))),
('30 Yrs old today', DateAdd(year, -30, GetDate())),
('30 Yrs old tomorrow', DateAdd(day, 1, DateAdd(year, -30, GetDate())));
 
With cte As
(Select EmpName, BirthDate, DateDiff(year, BirthDate, GetDate()) As ProvisionalAge
From @Test)
Select EmpName, BirthDate,
  ProvisionalAge - Case When DateAdd(year, ProvisionalAge, BirthDate) > GetDate() Then 1 Else 0 End As Age
From cte;

Explanation of Tom's solution:

This uses the fact that when you do a DATEDIFF() in years between birth date and the current date, the number you get is either the correct age or one year too large depending on whether or not the person has had a birthday in this year. That can be determined by adding that number of years to the birth date and checking to see if you get a date greater than today. If so, subtract 1.

Solution #3 - Provided by Monica Rivera

Code Snippet
USE AdventureWorks
GO
 
select LoginID, BirthDate,
CASE WHEN DATEADD(YY,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate)<GETDATE() THEN DATEDIFF(yy,BirthDate,GETDATE())
ELSE DATEDIFF(yy,BirthDate,GETDATE())-1 END AS AGE
from HumanResources.Employee
order by BirthDate

Explanation of Monica's solution:

There are two cases when calculating a person’s age:

1. The birth date has already happened in the current year: the age is simply the current year minus the year of birth.
2. The birth date has not yet happened in the current year: the age is equal to the current year minus the year of birth minus one.

A simple T-SQL expression to determine if the birth date has already happened in the current year is the following:

DATEADD(YY,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate)<GETDATE()

This adds to the birth date the difference in years between to the birth date and today’s date. If this exceeds today’s date, then we are in case 2.

Plug it in to a case statement that describes the two cases above and you get the solution.

Solution #4 - Provided by Steven Wang

Code Snippet
--Extract some Test Data using the AdventureWorks sample database
If Object_ID('dbo.employee_birthdate') is not Null
Drop table dbo.employee_birthdate;
  SELECT[NationalIDNumber]
  , [BirthDate]
Into dbo.employee_birthdate
FROM [HumanResources].[Employee]
Union All
Select -111111, '2000-02-29'
Union All
Select -222222, '2004-02-29'
Union All
Select -333333, Cast(Getdate() as Date);
--Actual solution
SELECT [NationalIDNumber], [BirthDate]
  ,(Convert(Char(8), CURRENT_TIMESTAMP,112) - 0 - Convert(char(8), [BirthDate], 112)) / 10000 As Age
FROM [dbo].[employee_birthdate]
Order by Age;

Explanation of Steven's solution:

To calculate the age based on birth date needs to take account of 2 considerations: year difference and the date was born.
Use the year difference as the base of the age calculation. If the current date is less than the date was born, then the age needs to minus 1 year.
Inspired by the dimensional surrogate integer date key, we can use the date integer format to perform the calculation. For example, if a birthdate is ‘2001-09-30’, and current date is ‘2012-10-01’, then we can use the difference (20121001 – 20010930) / 10000 to get the age 11. As in T-SQL Integer / Integer is always Integer. In the same example, if the current date is ‘2012-09-29’, then the result (20120929 – 20010930) / 10000 will be 10.
As we can’t directly convert the date format to integer value, I convert the date to char(8) first and use minus 0 to implicitly convert the char value to integer value.

Conclusion:

As you can see, all four of the above solutions provide the result we were looking for, but do so in creatively different styles. Each of these solutions highlights the power and flexibility that SQL Server provides. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Olaf, Tom, Monica, and Steven for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Steven Wang has worked with SQL server for more than 10 years. Steven is an active SQL server community participant and speaks at events such as TechEd, CodeCamp, SQL User Group etc.

Blog: www.MSBICOE.com | LinkedIn: Steven Wang | MSDN Profile: Steven Wang - Shangzhou

Tom Cooper began his programming career in 1968, began working with database software in 1977, and first worked with Microsoft SQL Server in 1994 (version 4.21). He is now very happily retired.

Monica Rivera has been working with SQL Server for the past 10 years. She worked as a DBA before joining Microsoft, where she works as a tester for the SQL Server Enterprise & Tier 1 Manageability group. She is very active in both the English and Spanish SQL Server MSDN forums.

TSQL - Solve it YOUR Way - Parsing Tips and Tricks - Returning Digits after the Decimal Point

2012-10-23T19:15:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today we will tackle a numeric parsing question. While past topics include solutions that vary from 15-150 lines of TSQL code, all of today's solutions will be very short. A question was recently posted in the MSDN forums asking how to retrieve the digits after the decimal for a numeric data type. Off the top of our heads, many people can probably come up with a few solutions. After sending the question out to the blog series contributors, we came up with ten solutions that highlight creative parsing solutions and some less known tips and tricks within T-SQL.

Topic: Given a numeric value, return the digits after the decimal (ex: 3.142 returns 142)

Solutions - provided by Tom Cooper, Olaf Helper, Kent Waldrop, Jingyang Li, & Steven Wang

Code Snippet
-- Test data
Declare @Test Table(Foo decimal(9,3));
Insert @Test(Foo) Values (1.479), (2.000), (5.123);
 
-- Solution 1:  ParseName
Select Foo, ParseName(Foo, 1) From @Test;
 
-- Solution 2:  Modulo / Absolute Value 
SELECT SUBSTRING(CONVERT(varchar(30), ABS(Foo) % 1), 3, 30) AS Result FROM @Test
 
-- Solution 3:  Slightly modified version using modulo.  Note:  This solution returns '0' for input 2.000, whereas others return '000'
select Foo, cast(sign(Foo) * 1000 * (Foo % 1) as integer
  ) as decimal_Part
from @test
 
-- Solution 4:  Reverse / Floor solution
SELECT REVERSE(CAST(FLOOR(REVERSE(ABS(Foo))) AS bigint)) from @Test
 
-- Solution 5:  Use the original number minus an integer converted from the original number to an integer and then convert the decimal number to a varchar (50) to use the STUFF function to remove the '0.' part from the decimal number.
SELECT Stuff(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), 1, 2, '') from @Test
 
-- Solution 6:  Remove the "0." part from the above calculation with a substring function
SELECT Substring(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), 3, Len(Foo)) from @Test
 
-- Solution 7:  Use right function to remove the leading "0." from the string
SELECT RIGHT(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), Len(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50))) - 2) from @Test
 
-- Solution 8:  Find the . position using charindex and stuff everything before decimal 
  SELECT Stuff(Foo, 1, Charindex('.', Foo), '')from @Test
 
-- Solution 9
SELECT Stuff(Foo, 1, Charindex(0X2E, Cast(Ltrim(Foo) AS VARBINARY(50)), 1), '') from @Test
 
-- Solution 10:  CAST the string as an XML type and retrieve the value from the XML node
SELECT Cast (N'<root><row>' + Replace(Foo, '.', '</row><row>') + '</row></root>' AS XML).value('(/root/row)[2]', 'nvarchar(50)') from @Test

Conclusion:

It is very common to encounter situations where parsing is required in some capacity. The solutions presented above cover common parsing techniques such as SUBSTRING and PARSENAME, but also leverage other constructs such as STUFF, REVERSE, Modulo, and others in creative ways. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Tom, Olaf, Jingyang, Steven, and Kent for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

TSQL - Solve it YOUR Way - Finding the Longest Repeated Substring

2012-10-10T23:29:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic will cover a common programming interview question where we are asked to find the longest repeated substring in a given string, followed by different solutions and explanations from three of the more helpful and creative contributors in the TSQL MSDN forums, Steven Wang, Kent Waldrop, and Jingyang Li.

Topic: Given a string, find the longest repeated substring

I was asked this question many years ago in a programming interview and I solved it in C# as was expected in the context of the role I was interviewing for. A few days ago, a friend of mine was discussing a recent programming interview where he was asked this same question. While discussing solutions with him, I realized that this could make for a great topic for the Solve it YOUR Way series. While T-SQL may be an unlikely method of solving this problem, the solutions below highlight the power of SQL Server through the creative solutions of the authors.

As with any good interview question, the initial phrasing of the question does not provide all of the necessary requirements to provide the best or only correct solution. In this case, in order to provide a correct solution, we would need details such as 1) can substrings overlap, and 2) what do we return if multiple substrings of the same length exist (first occurrence, all occurrences?), etc. I intentionally left these details out of the original topic to allow the answers a bit more freedom in their solutions. The solutions reflect these subtle differences.

Solution #1 - Provided by Steven Wang

Code Snippet
--First of all, we need a generic number table. I know that we can use the common table expression to create
--a virtual number table. But I still prefer to have a physical number table in place.
--As SQL server can only store up to 8000 characters for a string value, for simplicity I will create
-- a number table with maximum value equals to 8000.
If Object_ID('dbo.LongestStr_Number', 'U') Is Null
    Begin
        SELECT TOP 8000 IDENTITY(INT,1,1) AS Number
        INTO    dbo.LongestStr_Number
        FROM    master.sys.all_columns A
                    CROSS JOIN
                master.sys.all_columns B;
 
        ALTER TABLE dbo.LongestStr_Number ADD CONSTRAINT PK_LongestStr_Number PRIMARY KEY CLUSTERED (Number) WITH FILLFACTOR = 100;
    End
 
 
--Solution Starts from here. The solution works only for SQL server 2012, by some change can be easily used for SQL server 2005/08
Declare @MyString varchar(8000);
Declare @Length int;
 
Set @MyString = 'ABAB CDCDCDCD ABAB';
Set    @Length = Len(@MyString);
 
;With RepeatingNumber
AS
(
Select    A.Number, Row_Number() Over(Partition By A.Number Order By (Select 0)) AS Position
From    LongestStr_Number A
            Cross Apply
        (
            Select    1 As Col
            From    LongestStr_Number B
            Where    Number <= A.Number
        ) C
Where    A.Number between @Length/2 + 1 and @Length
)
, CTE
As
(
Select    A.Number, B.MySubString, B.StringLen
        , Count(B.MySubString) Over(Partition By A.Number, B.MySubString) As MyCount
        , A.Position - First_Value(A.Position) Over(partition By A.Number, B.MySubstring Order by A.Position Rows Unbounded Preceding) - B.StringLen As PositionGap
From    RepeatingNumber A
            CROSS APPly
        (    Select        Substring(@MyString, A.Position, @Length - A.Number + 1) As MySubstring
                        ,  @Length - A.Number + 1 As StringLen
        ) B
)
 
Select        Top 1 with Ties MySubstring As longest_repeating_String
From        CTE
Where        MyCount > 1 and PositionGap >= 0
Order by    StringLen Desc

Explanation of Steven's solution:

To get substrings of a string, the number of substrings is determined by the length of the substring. For a string with length equals to n, it has 1 substring if the length of the substring equals to n – 1 + 1. If the length of the substring equals to n – 2 + 1, then it can have 2 substrings with overlaps. With the same fashion, if the length of a substring equals to n – m + 1 (m<=n), a string will have m number of substrings with overlaps.

As our task is to find out the longest repeating substring with NO overlaps, the number m should be only starting from n/2 + 1 (remember in T-SQL integer n divided by 2 will always return back integer). However, this doesn’t guarantee the substrings without overlaps.

Since each m number has m number of substrings which is calculated by the RepeatingNumber virtual table Cross Applied with a substring function, we can use the window function Count () by partitioning the m number and the substring to find out the repeating substrings with overlaps. If the window function Count() Over returns back the value is greater than 1, then we find a repeating substring. But at this stage, we don’t know if the repeating substrings are overlapped or not.

I used another window function First_value to identify the repeating substrings without overlaps by using the code below:

A.Position - First_Value(A.Position) Over(partition By A.Number, B.MySubstring Order by A.Position) - B.StringLen

The idea of above code is to get the offset value of the starting point of the remaining substrings with same value as the first substring within the same Number group. If the offset value is negative, then we can conclude that the two substrings have overlaps. Otherwise, the two substrings are non-overlapped repeating substrings.

At the last we use the TOP 1 clause with order by to get the longest repeating substring.

I have to mention that the white spaces will be treated as characters in this solution. we can modify the solution to take care of it if it is needed.

Solution #2 - Provided by Kent Waldrop

Code Snippet
-- Create a numbers table for use in the solution
create table numbers(n int)
go
 
declare @counter int
set @counter = 0
while @counter < 400
begin
    set @counter = @counter + 1
    insert into numbers
    select @counter
end
 
-- Create a temporary table containing the strings for which we will find the longest substring
declare @test table(theString varchar(400));
insert into @test
select 'ABABABABABABABABABABABABABAB CDCDCDCDCDCDCDCDCDCDCDCDCDCD'
union all
select 'SQL Server 2012 has introduced several new window functions and enhanced support for window aggregate functions by introducing window order and frame clauses, support for offset functions. In this session, the presenter will apply these new functions to solve some most frequently asked questions in MSDN T-SQL forum.'
union all
select 'bananas'
union all
select '3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117067982148086513282306647093844609550582231725359408128481117450284102701938521105559644622948954930381964428810975665933446128475648233'
union all
select 'Call me Ishmael. Some years ago--never mind how long precisely--having little or no money in my purse, and nothing particular to interest me on shore, I thought I would sail about a little and see the watery part of the world. It is a way I have of driving off the spleen and regulating the circulation.'
;
 
-- Solution starts here
--;WITH Numbers AS
--(
--     SELECT 1 AS n
--     UNION ALL
--     SELECT n + 1 AS n FROM   Numbers  WHERE  n < 400
--)
--,
with findRepeatChars as
( select
    1 as stringLength,
    n as location,
      (len(theString)-n)/2 as diff,
    cast(substring(theString, n, 1) as varchar(200)) as MatchString,
      theString
  from @test
    outer apply
  ( select n from numbers
    where n < 400
     and n < len(theString)
      and charindex(substring(theString, n, 1), theString, n+1) > 0
  ) oa1
), primaryWork as
(
  select
    substring(theString, location, loop_Offset + max_N) as matchString,
    theString,
    rank() over
    ( partition by theString
      order by loop_Offset + max_N desc
    ) as Rn
  from findRepeatChars
  cross apply
  ( select
      iif(diff>=9,     8, null) as group2_Offset,
        iif(diff>=25,   24, null) as group3_Offset,
        iif(diff>=49,   48, null) as group4_Offset,
        iif(diff>=90,   89, null) as group5_Offset,
        iif(diff>=144, 143, null) as group6_Offset
  ) calcGroupOffsets
  cross apply
  ( select
      nullif(sign(charindex(substring(theString, location, group6_offset + 1),
            theString, location + group6_Offset + 1)), 0) as group6_Index,
      nullif(sign(charindex(substring(theString, location, group5_offset + 1),
            theString, location + group5_Offset + 1)), 0) as group5_Index,
      nullif(sign(charindex(substring(theString, location, group4_offset + 1),
            theString, location + group4_Offset + 1)), 0) as group4_Index,
      nullif(sign(charindex(substring(theString, location, group3_offset + 1),
            theString, location + group3_Offset + 1)), 0) as group3_Index,
      nullif(sign(charindex(substring(theString, location, group2_offset + 1),
            theString, location + group2_Offset + 1)), 0) as group2_Index
  ) calcGroupIndices
  cross apply
  ( select
      coalesce( group6_Index * group6_Offset, group5_Index * group5_Offset,
            group4_Index * group4_Offset, group3_Index * group3_Offset,
              group2_Index * group2_Offset, 0
        ) as loop_Offset,
      coalesce
          ( group6_Index * iif(diff>=144, 57, null), group5_Index * iif(diff>=90,55, null),
          group4_Index * iif(diff>=49,  42, null), group3_Index * iif(diff>=25,  25, null),
            group2_Index * iif(diff>=9,   17, null), 9
        ) as loop_Range
  ) calcLoopParms
  cross apply
  ( select
      max(n) as max_N
    from numbers
    where n <= 57
      and n <= loop_Range
        and charindex(substring(theString, location, loop_Offset + n), theString,
               location + loop_Offset + n)
        > 0
  ) calcMaxN
)
 
select
  matchString as longest_Substring,
  theString as input_String
from primaryWork
where Rn = 1
 
OPTION (maxrecursion 0)

Explanation of Kent's solution:

The purpose of this query is to find the longest substring that is repeated within a given string. Note that this query depends on a table of numbers. This query is divided into three major parts:

• The "findRepeatChars" CTE
• The "primaryWork" CTE
• The outer query

"findRepeatChars" locates all characters in the input string that are repeated at some point later in the string. This CTE parses the string by using a table of numbers to spin through each character position except the last position, which cannot have a repeated character that comes later in the string.

"primaryWork" processes the locations found by "findRepeatChars" and determines the longest substring beginning at the given location. This CTE includes four CROSS APPLY operators to provide reusable calculations:

• calcGroupOffsets
• calcGroupIndices
• calcLoopParms
• calcMaxN

calcGroupOffsets uses the built in IIF function to calculate offsets that can be used as a base locations for dividing a string into smaller segments that can be quickly processed with a linear search.

calcGroupIndices determines if a string at the given location has a substring of a length determined by the group offset that repeats later in the given string.

calcLoopParms uses COALESCE functions to select the largest group offset that defines a substring that is later repeated in the given string based on the computed group indices. In addition, the built in IIF function is used to determine an appropriate search range based on the selected group offset and group index.

calcMaxN uses the computed loop parameters and a table of numbers to determine the largest number that can be added to the offset. This largest number when added to the largest group offset determines the largest substring that is repeated at a given location in the string.

After the primaryWork CTE processes the four apply operators, a RANK function is used such that the rank is partitioned according to the input' string and ordered by the descending string lengths of all of the repeating substrings for a given input string to provide a row number (rn) column.

The outer query calls the primaryWork CTE to process a list of all repeating substrings associated with a given input string. Records are filtered with the outer query such that only records that have a row number (rn) of 1 are retained.

Notes:
I sort of used odd squares to segment out the data; I didn't investigate whether some kind of log method might be better.

Solution #3 - Provided by Jingyang Li

Code Snippet
declare @MyString varchar(max)
Set @MyString = 'Server 2012 has introduced several new window functions and enhanced support for window aggregate functions by introducing window order and frame clauses, support for offset functions. In this session, the presenter will apply these new functions to solve some most frequently asked questions in MSDN T-SQL forum.';
 
;WITH Numbers AS
(
     SELECT 1 AS i
     UNION ALL
     SELECT i + 1 AS i FROM   Numbers  WHERE  i < LEN(@MyString)
)
 
,mycte1 AS
  (
  SELECT a.i AS Step,b.i AS StartPos, SUBSTRING(@MyString, b.i,  a.i) AS SubSeq FROM Numbers a CROSS JOIN Numbers b
  )
 
,mycte2 AS (
     SELECT  StartPos, Step, SubSeq
     ,Count(SubSeq)over(Partition by SubSeq) cntSubSeq
     ,ROW_NUMBER() Over(Partition by SubSeq Order by Step ) rnSubSeq
     ,MAX(Len(SubSeq)) OVER(Partition by SubSeq) maxLen
  FROM  mycte1  WHERE  LEN(SubSeq) >= Step
)
 
,mycte3 as
(
    SELECT Step,StartPos, SubSeq,  maxLen, rnSubSeq
    FROM   mycte2
    WHERE cntSubSeq>1
)
 
,mycte4 as
(
    SELECT  a.SubSeq, a.StartPos, a.maxLen
           ,RANK() OVER (order by a.maxLen DESC) AS rnkLen
           ,Row_Number() OVER (order by a.maxLen DESC,a.Step) AS rnLen
    FROM mycte3 a
    LEFT JOIN mycte3 b ON a.SubSeq=b.SubSeq and (a.rnSubSeq=b.rnSubSeq-1 OR b.rnSubSeq-1 = 0)
    WHERE (a.StartPos+a.Step-1) < b.StartPos
)
 
    SELECT SubSeq as [LognestRepeatingSubstring] FROM mycte4
    WHERE rnkLen=1
    Order by maxLen DESC, StartPos
 
OPTION (maxrecursion 0)

Explanation of Jingyang's solution:

Step 1: Create a numbers table based on the string length but it would be better to use an auxiliary numbers table;
Step 2: Cross join Numbers table to create positions and steps for all substring combinations;
Step 3: Calculate the occurrence of substrings in various lengths, the length for each substring and a sequence within a substring based on the step. The WHERE condition is to eliminate double count for any substrings after the last full step increments;
Step 4: Get all substrings with at least two occurrences;
Step 5: Remove repeating substring with overlapping and get the ranking order for the longest substrings;
Step 6: Retrieve the longest repeating substring(s) with two options.

The option1 is to get longest string with tie breaker (first occurrence) by using rnLen=1 and the second option is to get longest strings without tie breaker by using rnkLen=1;

In order to change the default recursion limit from 100 to no limit for CTEs, the query hints MAXRECURSION is set to 0.

Note: I include the rank order to choose the first occurrence or all longest repeating substrings in the query.

Conclusion:

As you can see, all three of the above solutions provide variations of the result we were looking for, but do so in creatively different styles. Each of these solutions leverages different SQL Server language constructs and includes different considerations in the final solutions, including one solution that leverage new constructs from SQL Server 2012. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Jingyang, Steven, and Kent for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Steven Wang has worked with SQL server for more than 10 years. Steven is an active SQL server community participant and speaks at events such as TechEd, CodeCamp, SQL User Group etc.

Blog: www.MSBICOE.com | LinkedIn: Steven Wang | MSDN Profile: Steven Wang - Shangzhou

Kent Waldrop started working with Sybase Transact SQL in 1989 as an application developer and continued working with Sybase until 1995 when SQL Server 6 was released. At that time, he became a Microsoft SQL Server database administrator and has continued to work with Microsoft SQL Server ever since. Currently he is a database architect working with Microsoft SQL Server, Oracle and UDB/DB2.

Jingyang Li has been working with SQL Server since he began his IT career as an ASP.NET developer in 2001. He enjoys working with T-SQL and recently took a full time job as a DBA. He has been participating in the Microsoft forums with alias Limno.

TSQL - Solve it YOUR Way - Finding the percentage of NULL values for each column in a table

2012-09-22T15:29:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic will cover a question asked in the Transact-SQL MSDN Forums here, followed by three different solutions from three of the more helpful and creative contributors in the TSQL MSDN forums, Jens Suessmeyer, Naomi Nosonovsky, and Jingyang Li.

Topic: How can I find the percentage of NULL values for each column in my table?

For various reasons, people may need to determine the percentage of values in each column that are NULL. Perhaps you are trying to calculate the percentage to determine if your table could save disk space by leveraging SPARSE columns. In another real-world example from the forums, a user had an implementation where partial rows in a table were being updated at different times. When all rows were updated with a value and were thus NOT NULL, the table was considered "complete" and could be processed. For this reason, the customer was trying to determine when the percentage of NULL values hit 0%. In yet another case, the user wanted to present a comparison chart throughout the entire database showing the ratio of NULL values to NOT NULL values. For the final example, take a look at the explanation provided below by Jens where he solved this problem at a customer site.

Solution #1 - Provided by Jens Suessmeyer

Code Snippet
SET NOCOUNT ON
DECLARE @Statement NVARCHAR(MAX) = ''
DECLARE @Statement2 NVARCHAR(MAX) = ''
DECLARE @FinalStatement NVARCHAR(MAX) = ''
DECLARE @TABLE_SCHEMA SYSNAME = 'Production'
DECLARE @TABLE_NAME SYSNAME = 'Product'
SELECT
        @Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,
        @Statement2 = @Statement2 + COLUMN_NAME + '*100 / OverallCount AS ' + COLUMN_NAME + ',' + CHAR(13) FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA
IF @@ROWCOUNT = 0
        RAISERROR('TABLE OR VIEW with schema "%s" and name "%s" does not exists or you do not have appropriate permissions.',16,1, @TABLE_SCHEMA, @TABLE_NAME)
ELSE
BEGIN
        SELECT @FinalStatement =
                'SELECT ' + LEFT(@Statement2, LEN(@Statement2) -2) + ' FROM (SELECT ' + LEFT(@Statement, LEN(@Statement) -2) +
                ', COUNT(*) AS OverallCount FROM ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ') SubQuery'
        EXEC(@FinalStatement)

Explanation of Jens' solution:

To put a bit more context to my solution, I was at a customer site where they found out that their API was occasionally losing the information on the way from the front end to the database, therefore NULLing out specific fields which should not be NULL. While they checked through their database and wrote queries for their hundreds of tables, they asked me working for another work stream to write something they can use. The simple idea is to grab a Table Name (and of course the appropriate schema) and create a Dynamic SQL statement which will calculate the percentage of NULL values in a specific table.

From the query the following information will be returned:

In detail we see that the Color (on purpose or not) is not always entered showing a 49% of NULL values in that column. 58% for Size, 65% for SizeUnitMeasureCode, etc. The rest does have (at least NON NULL) values. If you would be also interested in the percentage of that are NULL OR Empty the query can be easily tweaked by changing the source code the following line from:

@Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,

@Statement = @Statement + 'SUM(CASE WHEN LEN(ISNULL(' + COLUMN_NAME + ',' + REPLICATE(CHAR(39),2) + ')) = 0 THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,

Leading to a change in the generated code of:

SUM(CASE WHEN LEN(ISNULL(CompanyName, '')) = 0 THEN 1 ELSE 0 END) AS CompanyName,

Feel free to include your own logic and forensic pattern to find all the black holes in your databases.

Solution #2 - Provided by Naomi Nosonovsky

Code Snippet
DECLARE @TotalCount DECIMAL(10, 2)
    ,@SQL NVARCHAR(MAX)
SELECT @TotalCount = COUNT(*)
FROM [AdventureWorks].[Production].[Product]
SELECT @SQL = STUFF((
            SELECT ', CAST(SUM(CASE WHEN ' + Quotename(C.COLUMN_NAME) + ' IS NULL THEN 1 ELSE 0 END) * 100.00 
/@TotalCount AS decimal(10,2)) AS [' + C.COLUMN_NAME + ' NULL %]
'
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE TABLE_NAME = 'Product'
                AND TABLE_SCHEMA = 'Production'
            ORDER BY C.ORDINAL_POSITION
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 2, '')
SET @SQL = 'SET @TotalCount = NULLIF(@TotalCount,0) 
SELECT ' + @SQL + '
FROM [AdventureWorks].Production.Product'
PRINT @SQL
EXECUTE SP_EXECUTESQL @SQL
    ,N'@TotalCount decimal(10,2)'
    ,@TotalCount

Explanation of Naomi's solution:

To get the percent of null values in a column we use this formula

sum(case when ColumnName IS NULL then 1 else 0 end) * 100.00 / @TotalCount

where @TotalCount is the number of rows in a table.
We need to generate the above statement dynamically, so we use INFORMATION_SCHEMA.Columns view to do so.
I use FOR XML PATH('') approach to concatenate information into one variable
I use sp_executeSQL system stored procedure to run the dynamic SQL in order to supply a variable. Alternatively, of course, I could have declared and calculated @TotalCount inside the dynamic SQL.

Solution #3 - Provided by Jingyang Li

Code Snippet
SET NOCOUNT ON;
   DECLARE @TABLE_NAMENVARCHAR(50) = 'Product',
        @TABLE_SCHEMA NVARCHAR(50) = 'Production',
        @sql          NVARCHAR(4000),
        @col          NVARCHAR(50)
CREATE TABLE #t
  (
     id      INT IDENTITY(1, 1),
     ColName VARCHAR(50),
     [NULL%] DECIMAL(8, 2)
  )
DECLARE c_cursor CURSOR FOR
  SELECT column_Name
  FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE  TABLE_NAME = @TABLE_NAME
         AND TABLE_SCHEMA = @TABLE_SCHEMA
         AND IS_Nullable = 'YES'
OPEN c_cursor;
FETCH NEXT FROM c_cursor INTO @col;
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @sql = N' INSERT INTO #t (ColName, [NULL%])
        SELECT TOP 1 ''' + @col
                 + ''' , (COUNT(*) over() *1.0- COUNT(' + @col
                 + ') OVER()*1.0)/COUNT(*) Over() *100.0 as [NULL%] FROM '
                 + Quotename(@TABLE_SCHEMA) + '.'
                 + Quotename( @TABLE_NAME)
      -- print @sql
      EXEC (@sql);
      FETCH NEXT FROM c_cursor INTO @col;
  END
CLOSE c_cursor;
DEALLOCATE c_cursor;
   SELECT ColName,  [NULL%] FROM#t
DROP TABLE #t

Explanation of Jingyang's solution:

Jingyang initially sent a solution very similar to that of Naomi, so in order to provide an additional technique, he also provided the CURSOR based solution above. The cursor is built by leveraging the INFORMATION_SCHEMA table as used in all three solutions, but Jingyang uses a nice optimization technique of only querying for Is_Nullable = 'YES' to only obtain NULLable fields. In the CURSOR loop, the solution relies on some creative use of the OVER() function to generate the percentage and results, which are displayed in a way that is very readable and concise.

Conclusion:

As you can see, all three of the above solutions provide the intended result we were looking for, but do so in creatively different styles, this time including our first CURSOR based solution. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Jens, Naomi, and Jingyang for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Jens Suessmeyer is an MCS Consultant in Germany and has been working with SQL Server since version 6.0. He is very active in the forums and in his blog (http://blogs.msdn.com/b/jenss), providing outstanding real-world solutions and insight.

Naomi Nosonovsky, Senior Software Developer, has more than 15 years of enterprise experience in analysis, design, development and implementation of high-performance client/server data management solutions. She is a Personality of the Year for 2008, 2009, 2010 and 2011 at www.universalthread.com in .NET, SQL Server & VFP categories. She is also an All Star contributor/MCC Community Contributor at forums.asp.net and MSDN T-SQL forum. She also actively blogs at http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=218 and http://beyondrelational.com/members/naomi/modules/2/posts.aspx?Tab=16.

TSQL - Solve it YOUR Way - Finding the MAX Value from Multiple Columns

2012-09-11T00:07:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic will cover a question recently asked in the Transact-SQL MSDN Forums here, followed by four different solutions from three of the more helpful and creative contributors in the TSQL MSDN forums, Jingyang Li, Steven Wang, and Kent Waldrop.

Topic: I have a table with three datetime columns and want to return the most recent date for each row.

In the following image, we see an example of our three datetime columns, Date1, Date2, and Date3, followed by a "MaxDate" column containing the latest date of the three. The following solutions will show us various ways to calculate this "MaxDate" value.

This is a great question for our blog series because there are so many ways to tackle this question. In fact, the forum post included his original solution using a CASE statement, but he was looking for additional ways to solve the problem. Let's take a look at four additional ways to accomplish this.

Solution #1 and Solution #2 - Provided by Jingyang Li

Code Snippet
create table Dates (id int identity(1,1), date1 datetime, date2 datetime, date3 datetime)
insert into Dates (date1,date2,date3)
values    ('1/1/2012','1/2/2012','1/3/2012'),
        ('1/1/2012',null,'1/13/2012'),
        ('1/1/2012','1/2/2012',null),
        ('8/30/2012','9/10/2012','1/1/2013'),
        (null,null,null)
--Solution #1 - UNPIVOT approach
SELECT a.id, b.latestdate FROM Dates a LEFT JOIN (SELECT id, Max(date123) AS latestdate
   FROM   (SELECT id,date1,date2, date3 FROMDates) src
       UNPIVOT (date123
         FOR dates IN ([date1],
                       [date2],
                       [date3])) unpvt GROUP BY id ) b on a.id=b.id
--Solution #2 - Using the new SQL Server 2012 IIF function
SELECT
NULLIF(IIF (date1>date2,
IIF(date1>date3,date1,date3),IIF(date2>date3,date2,date3)),
'1/1/1900') latestdate
FROM (
SELECT id,Isnull(date1, '1/1/1900') date1, Isnull(date2, '1/1/1900') date2, Isnull(date3, '1/1/1900') date3 FROM Dates) t

Explanation of Jingyang's solutions:

Solution #1: My first thought was to unpivot these columns and use max function to get the latest date for each row. Since you cannot easily take the max value of multiple columns in a table, we can use the unpivot function to accomplish this. To include all NULL dates, we can use a LEFT JOIN to generate the result set.

Solution #2: Later on I found that we can use SQL Server 2012 inline IIF function to compare all cases to get the latest date. Within the query, we use ISNULL and NULLIF to handle NULL values.

Solution #3 - Provided by Steven Wang

Code Snippet
--Test Data Preparation
Declare @MyTable Table (ID Int Identity(1,1), Date1 Date, Date2 Date, Date3 Date);
Insert Into @MyTable
Values ('2012-09-12','2012-07-25','2012-06-06')
, ('2013-03-22','2012-06-03','2013-03-11')
, (NULL,NULL,NULL)
, ('2012-10-22','2012-12-30','2012-05-29')
, (NULL,'2012-08-07',NULL)
, ('2012-05-05','2012-08-03','2012-09-16')
--Actual Solution
Select ID, Date1, Date2, Date3, MaxDate
from @MyTable A
   Cross Apply
  ( Select Max(MyDate) As MaxDate
   From (Values (Date1), (Date2), (Date3)) B(MyDate)
  ) C

Explanation of Steven's solution:

As we need to find the latest date among 3 columns, the method using the value comparison between 2 different columns is a little clumsy. How about if we need to do the same when we have
more than 3 columns to compare?

One logical way to perform this comparison is to UNPIVOT the 3 columns’ values into one column and then use the MAX aggregation function to get the latest date value. As Jingyang showed, it is easy to use the UNPIVOT clause to transpose the multiple column data into one row. However, UNPIVOT normally has performance issues so we will look for a different solution.

Here, I will use a table value constructor to perform the UNPIVOT. In SQL Server 2008 or later, we can use VALUES clause in a standard way as a table value constructor to create a derived table.

The idea is to pass three base table date columns into a derived table constructed by VALUES clause by using the CROSS APPLY clause. Each column passed into the derived table creates a row. We can then perform an MAX aggregation to get the latest date for each row from the base table inside the derived table.

This technically is fun and performance-wise will be better than using the UNPIVOT clause to transpose columns and perform aggregation.

Solution #4 - Provided by Kent Waldrop

Code Snippet
declare @test table
(test_Id int, date1 date, date2 date, date3 date);
insert into @test
select 1, null, null, null union all
select 2, null, '20120102', null union all
select 3, '20120101', '20120102', '20120103' union all
select 4, '20130322', '20120603', '20130311' union all
select 5, '20120912', '20120725', '20120606'
;
select
  test_Id,
  case when date12 >= coalesce(date3, date12) then date12 else date3
  end as greatest_Date
from @test
cross apply
( select case when date1 >= coalesce(date2, date1) then date1 else date2
         end as date12
) as xa1;

Explanation of Kent's solution:

My solution uses a CROSS APPLY to provide re-use of a derived column "date12". Date12 is the greater of date1 and date2.

A key trick that is involved with this query is the following two comparisons:
date1 >= coalesce(date2, date1)
date12 >= coalesce(date3, date12)

These two comparisons above are logically equivalent to these two comparisons:

date1 >= date2 or date1 is not null and date2 is null
date12 >= date3 or date12 is not null and date3 is null

The greatest date is selected by fetching the greater of date1 and date2 in the cross apply and then selecting the greater of date3 and the results of the date that was selected in the cross apply.

The main ideas driving this particular form of the query are (1) to make my additional operator a "Compute Scalar" operation rather than something else and (2) aim at code that is small in size to make the code easier to understand.

Other alternatives:

• Use a CTE or a derived table instead of the APPLY operator since the APPLY operator is not ANSI standard
• Use the expanded OR / AND comparisons rather than COALESCE

Conclusion:

As you can see, all four of the above solutions provide the result we were looking for, but do so in creatively different styles. Each of these solutions leverages different SQL Server language constructs and includes different considerations in the final solutions, including one solution that leverage new constructs from SQL Server 2012. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Jingyang, Steven, and Kent for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Steven Wang has worked with SQL server for more than 10 years. Steven is an active SQL server community participant and speaks at events such as TechEd, CodeCamp, SQL User Group etc.

Blog: www.MSBICOE.com | LinkedIn: Steven Wang | MSDN Profile: Steven Wang - Shangzhou

TSQL - Solve it YOUR Way - Gaps and Islands... With a Twist!

2012-09-05T04:06:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic is a variation of the popular T-SQL question titled "Gaps and Islands" followed by three different solutions presented by three of the more helpful and creative contributors in the TSQL MSDN forums, Steven Wang, Alejandro Mesa, and Tom Cooper.

Gaps and Islands questions revolve around sequential data with missing values. The term "gaps" refers to the ranges of missing values, while the team "islands" refers to the ranges of existing/sequential values. This topic takes on many forms and shows up occasionally in SQL Server interviews, with variations such as the following:

Find your favorite sports team's longest winning streak given the win/loss results
Given a table of natural numbers, return a two column table showing the missing numbers (ex: missing from range 6-8, 11-21, etc)
Find the missing Order IDs given an Orders table with sequential Order IDs
Write a function that returns the hours worked in the past month by a specific employee

Topic: Given a series of integer ranges representing painted segments of a bridge, find the areas of the bridge where there is no paint, as well as the areas where the paint has been overlapped.

Recently, a question was asked in the TSQL MSDN forums that was a variation of the gaps and islands question, with a small twist. The question revolves around a bridge of length 100 that has been painted in various segments as shown with the following data:

For this exercise, we need to determine how to return the gaps (the areas where the bridge is NOT painted) as well as where the paint has been overlapped.

Solution #1: Provided by Steven Wang

Code Snippet
/****************************************************************
Solution Preparation:
1. Create a temp table #BridgeP
2. Create a temp table #BridgeMaxLength to hold the max length of all bridges
3. Insert some test data
4. Create nonclustered index to boost the performance for large volume of data.
 
Note: the solution I presented here is inspired from the book: 
      Inside Microsoft SQL Server 2008: T-SQL Programming
****************************************************************/
Create Table #BridgeP(    ID Int Identity(1,1) Not Null
                        , BridgePID int Not Null
                        , FromLen Decimal(14,4) Not Null
                        , ToLen Decimal(14,4) Not Null
                        , Constraint pk_BridgeMeaurement Primary Key (ID)
                        , Constraint check_From_Less_than_to Check(FromLen <= ToLen)
                    );
 
Create Table #BridgeMaxLength ( BridgePID int Not Null
                                , BridgeMaxLen Decimal(14,4)
                            );
 
Insert into #BridgeP
Values (1, 10.0002, 20.0004)
, (1, 18, 21.0004)
, (1, 11.1234, 20.0002)
, (1, 40, 50.0003)
, (1, 19.11, 35)
, (2, 46.2966, 50.6472)
, (2, 31.7084, 46.1286)
, (2, 178.2604, 190.6864)
, (2, 38.7394, 43.7234)
, (2, 27.266, 43.3031)
, (2, 122.8653, 142.6958)
, (3, 14.7856, 21.6676)
, (3, 67.6054, 68.1884)
, (3, 64.8543, 66.4028)
, (3, 123.331, 134.4094)
, (3, 199.3904, 201.1482)
, (3, 88.1347, 103.9153);
 
Insert into #BridgeMaxLength
Values (1, 100.0000)
, (2, 200.0000)
, (3, 250.0000);
 
  Create Nonclustered Index idx_Bridge_From_To On#BridgeP (BridgePID, FromLen, ToLen);
  Create Nonclustered Index idx_Bridge_To_From On#BridgeP (BridgePID, ToLen, FromLen);
 
/**********************************************************************************
Solution starts here:
 
1. Since the bridge paint measurement records have overlaps, we first need to identify
all those paint starting points for a bridge. These starting points must not be adjacent
to any end painting points recorded and must be less than any other starting points within
the same packed painting group.
**********************************************************************************/
 
;With RangeStart
As
(
Select    Distinct A.BridgePID, A.FromLen As RangeSTart
From    #BridgeP A
Where    Not Exists
            (    Select    *
                From    #BridgeP B
                Where    B.BridgePID =  A.BridgePID
                        And B.FromLen < A.FromLen
                        And B.ToLen >= A.FromLen
            )
)
/**********************************************************************************
2. With the same logic as starting point but in a reversed way, the ending points
can be identified as below:
**********************************************************************************/
, RangeEnd
As
(
Select    Distinct A.BridgePID, A.ToLen As RangeEnd
From    #BridgeP A
Where    Not Exists
        (    Select    *
            From    #BridgeP B
            Where    B.BridgePID =  A.BridgePID
                    And B.ToLen > A.ToLen
                    And B.FromLen <= A.ToLen
        )
)
/**********************************************************************************
3. Since a bridge should always start from 0 and end with the maximum length, we add
these 2 points together with all starting points and end points.
The union all query results must come back with an even number (pairs of start and 
end point) for each bridge.
4. In order to put starting point and an end point pair into the same group. I used
row_Number function:
(Row_Number() Over(Partition By BridgePID Order by GapStart) - 1) / 2
If the row number is 1, it returns back 0; if the row number is 2, it also return back
0. 
**********************************************************************************/
, GapValue
As
(
Select    BridgePID, GapStart
        , (Row_Number() Over(Partition By BridgePID Order by GapStart) - 1) / 2 As MyGroup
From
        (
            Select    BridgePID, 0.0000 As GapStart
            From    #BridgeMaxLength
                Union All
            Select    BridgePID, RangeStart
            From    RangeStart S
                Union All
            Select    BridgePID, RangeEnd
            From    RangeEnd E
                Union All
            Select    BridgePID, BridgeMaxLen
            From    #BridgeMaxLength
        ) X
)
 
Select        BridgePID, Min(GapStart) As GapStart, Max(GapStart) As GapEnd
From        GapValue
Group By    BridgePID, MyGroup
Having        Min(GapStart) <> Max(GapStart)
Order by    BridgePID, GapStart;
 
 
Drop Table #BridgeP;
Drop Table #BridgeMaxLength;

Explanation of Steven's solution:

As documented in the comments throughout the solution, Steven takes the following approach.

Since the bridge paint records have overlaps, we first need to identify all of those paint starting points for the entire bridge. These starting points must not be adjacent to any end painting points recorded and must be less than any other starting points within the same packed painting group.
Using similar logic, but in a reversed order, we will identify the ending points.
Since a bridge should always start from 0 and end with the maximum length, we add these 2 points together with all starting points and end points. The union all query results must come back with an even number (pairs of start and end point) for each bridge.
In order to put starting point and ending point pairs into the same group, I use the Row_Number function:
(Row_Number() Over(Partition By BridgePID Order By GapStart) - 1) / 2
If the row number is 1 or 2, then return 0.

Solution #2: Provided by Alejandro Mesa

Code Snippet
SET NOCOUNT ON;
USE tempdb;
GO
Declare @BridgeP Table(
BridgePID int,
FromLen Decimal(14,4),
ToLen Decimal(14,4)
);
 
Declare @MaxBridgeLenght Decimal(14,4) = 100.0000;
 
Insert into @BridgeP
Values
  (1,10.0002,20.0004)
, (2,18.0000,21.0004)
, (3,11.12345,20.0002)
, (4,40.0000,50.0003)
, (5,19.1100,35.0000)
;
 
WITH C0 AS (
SELECT
    FromLen, ToLen
FROM
    @BridgeP
UNION ALL
SELECT
    FromLen, ToLen
FROM
    (VALUES (0, 0), (100, 100) ) AS T(FromLen, ToLen)
)
, C1 AS (
SELECT
    FromLen AS ln,
    +1 AS [type],
    1 AS sub
FROM
    C0
 
UNION ALL
 
SELECT
    ToLen AS ln,
    -1 AS [type],
    0 AS sub
FROM
    C0
)
, C2 AS (
SELECT
    ln,
    SUM([type]) OVER(
    ORDER BY ln, [type] DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) - sub AS cnt
FROM
    C1
)
, C3 AS (
SELECT
    ln, ((ROW_NUMBER() OVER(ORDER BY ln) - 1) / 2) + 1 AS grp
FROM
    C2
WHERE
    cnt = 0
)
, C4 AS (
SELECT
    MIN(ln) AS FromLen,
    MAX(ln) AS ToLen
FROM
    C3
GROUP BY
    grp
)
, C5 AS (
SELECT
    *,
    LEAD(FromLen, 1, ToLen) OVER(ORDER BY FromLen) AS nxt_FromLen
FROM
    C4
)
SELECT
    ToLen AS GapStart,
    nxt_FromLen AS GapEnd
FROM
    C5
WHERE
    nxt_FromLen - ToLen > 0.0001;
GO
 
/*
 
GapStart    GapEnd
0.0000    10.0002
35.0000    40.0000
50.0003    100.0000
 
*/

The beauty of this approach is that it could easily be adapted to report on multiple bridges or roads, whatever it is that you are painting, as in the following modified example.

Code Snippet
SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @BridgeP TABLE (
BridgeID int NOT NULL,
BridgePID int,
FromLen Decimal(14,4),
ToLen Decimal(14,4)
);
 
Insert into @BridgeP
Values
  (1, 1,10.0002,20.0004)
, (1, 2,18.0000,21.0004)
, (1, 3,11.12345,20.0002)
, (1, 4,40.0000,50.0003)
, (1, 5,19.1100,35.0000)
 
, (2, 1,10.0002,20.0004)
, (2, 2,18.0000,21.0004)
, (2, 3,11.12345,20.0002)
, (2, 4,40.0000,50.0003)
, (2, 5,25.1100,35.0000)
 
;
 
-- Itzik's approach
WITH C0 AS (
SELECT
    BridgeID, FromLen, ToLen
FROM
    @BridgeP
UNION ALL
SELECT
    B.BridgeID, T.FromLen, T.ToLen
FROM
    (SELECT DISTINCT BridgeID FROM @BridgeP) AS B
    CROSS JOIN
    (VALUES (0, 0), (100, 100) ) AS T(FromLen, ToLen)
)
, C1 AS (
SELECT
    BridgeID,
    FromLen AS ln,
    +1 AS [type],
    1 AS sub
FROM
    C0
 
UNION ALL
 
SELECT
    BridgeID,
    ToLen AS ln,
    -1 AS [type],
    0 AS sub
FROM
    C0
)
, C2 AS (
SELECT
    BridgeID,
    ln,
    SUM([type]) OVER(
    PARTITION BY BridgeID
    ORDER BY ln, [type] DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) - sub AS cnt
FROM
    C1
)
, C3 AS (
SELECT
    BridgeID,
    ln,
    ((ROW_NUMBER() OVER(PARTITION BY BridgeID ORDER BY ln) - 1) / 2) + 1 AS grp
FROM
    C2
WHERE
    cnt = 0
)
, C4 AS (
SELECT
    BridgeID,
    MIN(ln) AS FromLen,
    MAX(ln) AS ToLen
FROM
    C3
GROUP BY
    BridgeID,
    grp
)
, C5 AS (
SELECT
    BridgeID,
    FromLen,
    ToLen,
    LEAD(FromLen, 1, ToLen) OVER(PARTITION BY BridgeID ORDER BY FromLen) AS nxt_FromLen
FROM
    C4
)
SELECT
    BridgeID,
    ToLen AS GapStart,
    nxt_FromLen AS GapEnd
FROM
    C5
WHERE
    nxt_FromLen - ToLen > 0.0001;
GO
 
/*
 
BridgeID    GapStart    GapEnd
1    0.0000    10.0002
1    35.0000    40.0000
1    50.0003    100.0000
2    0.0000    10.0002
2    21.0004    25.1100
2    35.0000    40.0000
2    50.0003    100.0000
 
*/

Alejandro's Explanation:

I see here two problems in one. The first one would be finding overlapping ranges, and then identifying gaps.

Itzik Ben-Gan included these two problems in his last book about window functions. He explains the solutions for SQL Server 2012 and also for previous versions in the following book:

Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions
http://shop.oreilly.com/product/0790145323088.do

I am going to use his approach to tackle your question since I didn't see any solution using the new support to the OVER clause (window functions).

Finding gaps using LEAD function is simple and I think that reading the code is enough. For the solution about packing overlapping ranges, the key is in splitting the range (each row) in two sets, one for the initial of the range (FromLen) and another for the end (ToLen), and adding a type (+1 or -1) depending if it is the start (FromLen) or the end of the range (ToLen). We are going to calculate the running total of [type] minus the [sub], and we will be interested only on those where the formula ([cnt]) is zero. This will give us pairs of start and end, and we can identify the pairs by enumerating those rows and using ((ROW_NUMBER() OVER(...) - 1 / 2) + 1), the rest is grouping and calculating the MIN and MAX per group.

Note: I didn't include an ORDER BY clause for presentation purpose, neither setup proper indexes to support the OVER clause.

Solution #3: Provided by Tom Cooper

Code Snippet
-- Sample data
Declare @Bridge Table(BridgePID int, fromlen int, tolen int);
Insert @Bridge(BridgePID, fromlen, tolen) Values
(1             , 10         ,20),
(2             ,18          ,2),
(3            ,11           ,20),
(4            ,40           ,50),
(5             ,19          ,35);
 
-- Create Numbers cte
;With N2 As (Select 1 As Number Union All Select 1),
N4 As (Select na.Number From N2 na Cross Join N2 nb),
N16 As (Select na.Number From N4 na Cross Join N4 nb),
N256 As (Select na.Number From N16 na Cross Join N16 nb),
Numbers As (Select Row_Number() Over (Order By n.Number) As Number From N256 n),
-- Now get only the missing numbers and a row_number value over those numbers
MissingNumbers As
(Select n.Number, Row_Number() Over (Order By n.Number) As SeqNbr
From Numbers n
Where n.Number <= 100 And Not Exists(Select * From @Bridge b Where n.Number Between b.fromlen And b.tolen))
-- Now product the islands report
Select Min(m.Number) As GapStart, Max(m.Number) As GapEnd
From MissingNumbers m
Group By m.Number - m.SeqNbr;

Tom's Explanation:

As noted in the original forum post, this is essentially an “Islands and Gaps” problem. The desired result is all the islands of unpainted points. But the input data is somewhat atypical. In a typical islands and gaps problem, the data is a series of points (for example, you have a table where each row had two columns – employeeid and sickday (datatype date)). And you want a report show the ranges of days an employee was out sick. So that you could produce a report like Employee 5 was out Jan 5 thru Jan 7. There is a good deal of information on how to solve this type of problem available both online and in books. One good discussion is by Itzik Ben-Gan at http://www.manning.com/nielsen/SampleChapter5.pdf.

So if the input data was a set of rows showing all the unpainted points, using that technique would solve our problem. But instead, the data is a series of ranges of painted points (possibly overlapping). So we want to first find all the points which are not in any of those ranges. To do this we need to first get all numbers between the minimum and maximum points (1 and 100 in this case). We do that either by using a Numbers table that was previously created or by building a Numbers cte as part of the query. I choose to build one with a cte since that way I did not have to assume the OP had a permanent numbers table. The method I used is a common one, the earliest reference I know to it is also by Itzik Ben-Gan at http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers.
Once I had this table of numbers I then kept only the numbers which were in the range 1 to100 and were not in any of the painted ranges in the original data. What’s left is a standard islands and gaps problem.

Conclusion:

As you can see, all three of the above solutions provide the result we were looking for, but do so in a very different style. The original thread provides variations of the solutions presented here as well as further discussion. Each of these solutions leverages different SQL Server language constructs and includes different considerations in the final solutions. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Steven, Alejandro, and Tom for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Steven Wang has worked with SQL server for more than 10 years. Steven is an active SQL server community participant and speaks at events such as TechEd, CodeCamp, SQL User Group etc.

Blog: www.MSBICOE.com | LinkedIn: Steven Wang | MSDN Profile: Steven Wang - Shangzhou

Alejandro Mesa has been working with SQL Server for more than 10 years, being an MVP since 2007. His passion is T-SQL and he is active in both English and Spanish forums under the alias of Hunchback.

Tom Cooper began his programming career in 1968, began working with database software in 1977, and first worked with Microsoft SQL Server in 1994 (version 4.21). He is now very happily retired.

TSQL - Solve it YOUR Way - Text to Speech

2012-08-28T19:11:00Z

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, I will present a topic recently discussed in the Transact-SQL MSDN forum here, followed by three different solutions to this problem supplied by three of the most active and helpful contributors in the forums, Tom Cooper, Naomi Nosonovsky, and Kent Waldrop. More importantly, they have included thoughts as to how they arrived at each solution. By seeing the different solutions along with a glimpse into their thought process, you can compare the different approaches to the solutions and learn from each of them.

Topic: I am writing a Text to Speech application and want to parse integer values into digits and convert them into the text representation of each digit. Example: Convert 16498 into the string "one six four nine eight".

Solution #1: Provided by Tom Cooper

Code Snippet
-- Test Data
Declare @Test Table(InputString varchar(10));
Insert @Test(InputString) Values ('164285'), ('1746');
 
-- Solution
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
InputString, '0', 'Zero '), '1', 'One '), '2', 'Two ')
, '3', 'Three '), '4', 'Four '), '5', 'Five ')
, '6', 'Six '), '7', 'Seven '), '8', 'Eight ')
, '9', 'Nine ')
FROM @Test;

Tom's Explanation:

The reason that I picked this solution is it is simple and easy to understand. That means it is easy to maintain. Maintainability is a very important consideration when developing software. So, for example, let's consider sometime in the future that this problem is extended to include the requirement that the number may have embedded commas, and/or the number may be preceded by a minus sign (-). If the requirement is to remove the comma’s and change the minus sign to the word “Minus”, then you could give this to a junior programmer who could easily understand this code and make the updates. Note, I have changed this slightly from my original answer. I have made the second argument to each REPLACE function call a string constant, not an integer constant. Either way works, but, of course, since REPLACE wants a string there, it is better to give it as a string rather than relying on the implicit conversion that will take place.

Solution #2: Provided by Naomi Nosonsovsky

Code Snippet
DECLARE @NumToText TABLE (
        Number TINYINT
        ,Word VARCHAR(10))
 
-- Create lookup table
INSERT INTO @NumToText
VALUES
(1,'One'),
(2,'Two'),
(3,'Three'),
(4,'Four'),
(5,'Five'),
(6,'Six'),
(7,'Seven'),
(8,'Eight'),
(9,'Nine'),
(0,'Zero')
 
-- Test values
DECLARE @Value VARCHAR(100) = '125790784533'
       ,@Output VARCHAR(max);
 
WITH cte
AS (
              SELECT SUBSTRING(@Value, N.Number, 1) AS [NumberToParse]
                     ,N.number
              FROM master..spt_values N where N.type = 'P'
                                        AND N.number BETWEEN 1
                                        AND LEN(@Value)
              )
              ,cte2
AS (
              SELECT cte.Number    
                    ,T.Word
              FROM @NumToText T
              INNER JOIN cte ON T.Number = cte.NumberToParse
              )
SELECT @Output = LTRIM((
                            SELECT ' ' + Word
                            FROM cte2
                            ORDER BY number
                            FOR XML PATH('')
                       ))
SELECT @Output AS Result 

Naomi's Explanation:

I first created a table that contains the mapping/conversion (ex: 1:one, 2:two, etc). Then using the numbers table, I parse the original value into individual digits using common table expressions (CTE's), join that with the conversion/lookup table, and then finally concatenate back using the XML PATH approach in the final @Output parameter.

Solution #3 - Provided by Kent Waldrop

Code Snippet
declare @test table(some_String varchar(10) );
insert into @test
select '164285' union all select '0987654321'
;
/* ------------------------------------------------
This query gets the name of a string of digits.   
(1) "name_List" provides a list of digit names as a string that is divided into 6-char segments -- a six-char segment 
that names each decimal digit.    
(2) "digits" fetches each individual digit of the input string.  
(3) "set_Names" fetches the name of each specific digit 
(4) the SELECT clause concatenates together the names obtained from "set_Names" to form the output string.
------------------------------------------------ */
select
some_String,
      coalesce(seg_1, '')
    + coalesce(seg_2, '')
    + coalesce(seg_3, '')
    + coalesce(seg_4, '')
    + coalesce(seg_5, '')
    + coalesce(seg_6, '')
    + coalesce(seg_7, '')
    + coalesce(seg_8, '')
    + coalesce(seg_9, '')
    + coalesce(seg_10, '')
from @test
cross apply
    ( select'zero one two three four five six seven eight nine '       
        as segments
) as name_List
cross apply
( select
        nullif(substring(some_String, 1, 1),'') as digit_1,
        nullif(substring(some_String, 2, 1),'') as digit_2,
        nullif(substring(some_String, 3, 1),'') as digit_3,
        nullif(substring(some_String, 4, 1),'') as digit_4,
        nullif(substring(some_String, 5, 1),'') as digit_5,
        nullif(substring(some_String, 6, 1),'') as digit_6,
        nullif(substring(some_String, 7, 1),'') as digit_7,
        nullif(substring(some_String, 8, 1),'') as digit_8,
        nullif(substring(some_String, 9, 1),'') as digit_9,
        nullif(substring(some_String,10, 1),'') as digit_10
) as digits
cross apply
( select
        rtrim(substring(segments, 6*cast(digit_1 as int) + 1, 6)) + ' ' as seg_1,
        rtrim(substring(segments, 6*cast(digit_2 as int) + 1, 6)) + ' ' as seg_2,
        rtrim(substring(segments, 6*cast(digit_3 as int) + 1, 6)) + ' ' as seg_3,
        rtrim(substring(segments, 6*cast(digit_4 as int) + 1, 6)) + ' ' as seg_4,
        rtrim(substring(segments, 6*cast(digit_5 as int) + 1, 6)) + ' ' as seg_5,
        rtrim(substring(segments, 6*cast(digit_6 as int) + 1, 6)) + ' ' as seg_6,
        rtrim(substring(segments, 6*cast(digit_7 as int) + 1, 6)) + ' ' as seg_7,
        rtrim(substring(segments, 6*cast(digit_8 as int) + 1, 6)) + ' ' as seg_8,
        rtrim(substring(segments, 6*cast(digit_9 as int) + 1, 6)) + ' ' as seg_9,
        rtrim(substring(segments, 6*cast(digit_10 as int) + 1, 6)) + ' ' as seg_10
) as set_Names;

Kent's Explanation:

I used the APPLY operators because it is a method of clarifying the code with which I am comfortable. Frequently, I will initially build a query in-line and then push complexity down into APPLY operators. This is a design alternative to use of CTEs or derived tables. However, if I am working with either Oracle or DB2 I tend toward the CTEs because the APPLY operator does not exist for either of these dialects. Notice how well the CROSS APPLY partitions the calculations into more easily understandable portions. Of course the same thing could be done with CTEs or derived tables.

There are a number of bookend operations that can be used for conditional processing. The most widely used combination is the CASE structure. Other potentials bookends include the use of NULLIF and COALESCE, the SIGN function and the IIF function if you using SQL Server 2012. In this case I chose to use the NULLIF and COALESCE bookends for conditional processing – but why?

Look again at the structure of the code. Notice that the COALESCE function is in the SELECT clause and that the NULLIF is in one of the CROSS APPLY clauses. By using these two operations to bookend my conditional code I can divide my code across a big boundary like this in order to reveal each distinct portion of the code.

Conclusion:

As you can see, all three of the above solutions provide the result we were looking for, but do so in a very different style. The original thread provides variations of the solutions presented here as well as one additional solution using a CASE statement. Each of these solutions leverages different SQL Server language constructs and includes different considerations in the final solutions. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Tom, Naomi, and Kent for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Tom Cooper began his programming career in 1968, began working with database software in 1977, and first worked with Microsoft SQL Server in 1994 (version 4.21). He is now very happily retired.

Kent Waldrop started working with Sybase Transact SQL in 1989 as an application developer and continued working with Sybase until 1995 when SQL Server 6 came out. At that time, he became a Microsoft SQL Server database administrator and has continued to work with Microsoft SQL Server ever since. Currently he is a database architect working with Microsoft SQL Server, Oracle and UDB/DB2.