I really don't mean for this blog to be T-SQL only, but recently the most work I've done that most easily translates to a blog has been T-SQL.  Today's entry is on the use of the PATINDEX function when converting and manipulating data.

Simply put, PATINDEX finds the first instance of a character in a given string.  This function is similar to CHARINDEX, except that you can specify a starting point.  The ability to specify a starting point makes it very useful in conversions. 

A recent example that I've had was data that had been entered as follows:  "John Q. Public".  I needed the data in three (3) different fields ("John", "Q.", "Public").  How was I to go about splitting up the data? 

Getting the First Name

The first name was easy.  I just used the PATINDEX to find the first instance of ' '.  The T-SQL looks like this:

    FirstName = Substring([fullname], 0, patindex( '% %', [fullname]))

This T-SQL says to take the [fullname] column, start at index 0 (the start), and give me everything until the index of the first space ('% %').  Very easy.

Getting the Middle Initial

Getting the Middle Initial is similar to getting the first name, except the 0 index starting point has to be replaced by the patindex of the first space.  In other words, we want the index of a space that occurs after the first instance of a space.  I know the text is confusing, but the code is worse.

     MI =
      substring (
            SUBSTRING([fullname], (patindex( '% %' , [fullname]) +1 ), LEN([fullname])) -- expession firstname
            , 1 --start
            , patindex( '% %' , SUBSTRING([fullname], (patindex( '% %' , [fullname]) + 1 ), LEN([fullname]))) -1 ) -- length firstname
          

Yes, that's ugly.  But essentially it's the same code as getting the first name, except now we're taking the taking the original script for finding the first name and slapping it in as the staring point for finding the Middle Initial.  As long as the open and closing brackets are kept track of, it's manageable.

Finding the Last Name

In theory, we could continue building on the above code to find the last name, but the code would get so confusing as to be unmanageable.  But, for fun, let's see what it would look like:

     LastName = ltrim(substring(
            [fullname] --expression
            , (patindex( '% %' , [fullname]) + len(substring (SUBSTRING([fullname], (patindex( '% %' , [fullname]) + 1 ), LEN([fullname]))
                  , 1, patindex( '% %' , SUBSTRING([fullname], (patindex( '% %' , [fullname]) + 1 ), LEN([fullname]))) -1 ))) + 2 -- start
            , len([fullname])
      )
      )

You can see the two previous example crammed into that code, but it's extremely hard to read and virtually impossible to maintain.  Let's find a better way.  If we think out of the box, the last name is all the characters after the LAST space in the string.  So if we find the last space, we can then just use substring.  The easiest way I've found to get the last space is to simple reverse the string and then use PATINDEX to find the first space.  I then subtract the PATINDEX from the length of the string, and now you have your starting point.

     LastName = substring(fullname, len(fullname) - patindex('% %', reverse(fullname)) + 2, patindex('% %', reverse(fullname)))

I added two (2) to the PATINDEX to compensate for the '.' and the extra space after the Middle Initial.

Anyway, that's all for this time.  I hope this article has been useful.  Happy coding!

RSS Feed http://www.damba.net

Flexible T-SQL:  Bending T-SQL to Get Around Everyday Programming Obstacles Part IV

This is a last blog in the “Flexible T-SQL” series and will be presented at the June 15, 2009 Macon .NET User’s group.  The other entries can be found here: Bitwise T-SQL Part I, Bitwise T-SQL PartII, Using TSQL Parameter Arrays

This post concerns the Partition Over function of T-SQL combined with Row_Number.  Incidentally, this topic was supposed to be the first blog post, but I happened to have projects that used the other topics so I wound up writing them first.

First we need to create a table and populate it with some test data.  We’ll create a log account that stored daily data on client balances.  Refer to the Create Table Test code at the bottom of the blog.  Now we’ve got three clients with three entries on three separate days.  What we want is the Balance of the account at the start of the day.  The standard Group By and Order by commands won’t return just three entries.  That’s where Partition Over and Row_Number comes in.

The Row_Number function does exactly what it says – it creates a Row Number as part of a select statement.  This function does not stand alone – it must be used in conjunction with Partition Over.  Use the Partition Over command to group the result set in the desired way. 

The format for the command is as follows:

Select Row_Number() Over (Partition by [Column], Order By [Column]) as [RowName] From [Table]

So we take that format and apply it against our data as follows:

SELECT   ClientID, Balance , SystemDateTime
, Row_Number() OVER(PARTITION BY ClientID, CONVERT(varchar(10), SystemDateTime, 101) Order By SystemDateTime asc) AS RowNumber
 FROM Test

ClientID    Balance               SystemDateTime          RowNumber
----------- --------------------- ----------------------- --------------------
1           1526.9761             2009-06-01 06:00:01.000 1
1           366.0195              2009-06-01 06:41:01.000 2
1           473.294               2009-06-01 16:04:59.000 3
1           1342.6356             2009-06-02 02:30:11.000 1
1           672.7358              2009-06-02 10:13:01.000 2
1           1442.8226             2009-06-02 17:06:01.000 3
1           1518.724              2009-06-03 03:08:31.000 1
1           1291.6467             2009-06-03 07:24:03.000 2
1           1657.9617             2009-06-03 16:31:35.000 3
2           1449.6962             2009-06-01 03:58:51.000 1
2           849.5199              2009-06-01 05:41:07.000 2
2           1356.5366             2009-06-01 08:36:49.000 3
2           67.9302               2009-06-02 10:09:53.000 1
2           1675.2756             2009-06-02 14:46:58.000 2
2           930.3946              2009-06-02 15:59:09.000 3
2           1574.3981             2009-06-03 01:36:33.000 1
2           401.0359              2009-06-03 09:47:22.000 2
2           652.8249              2009-06-03 12:15:56.000 3
3           1277.0665             2009-06-01 08:21:38.000 1
3           1257.7315             2009-06-01 09:03:16.000 2
3           1253.5927             2009-06-01 09:11:05.000 3
3           1473.4946             2009-06-02 07:44:24.000 1
3           130.657               2009-06-02 08:12:40.000 2
3           143.3953              2009-06-02 16:54:04.000 3
3           689.1654              2009-06-03 00:19:17.000 1
3           556.0171              2009-06-03 03:33:03.000 2
3           1182.3077             2009-06-03 09:58:59.000 3

That’s a lot of data.  Now let’s narrow it down to just the first record for each day by setting that select table as an inner table in a join.

--Use the Previous table as a join and get the first record for each client
Select dtA.ClientId
 , dtA.Balance
 , CONVERT(varchar(10), SystemDateTime, 101) as Date
From (
 SELECT
  ClientID
  , Balance
  , SystemDateTime
  , Row_Number() OVER(PARTITION BY ClientID, CONVERT(varchar(10), SystemDateTime, 101) Order By SystemDateTime asc) AS RowNumber
 FROM Test
 
 ) dtA
Where RowNumber = 1
Order by  Date, ClientId

Now we have the balance at the beginning of the day for each client.
ClientId    Balance               Date
----------- --------------------- ----------
1           1526.9761             06/01/2009
2           1449.6962             06/01/2009
3           1277.0665             06/01/2009
1           1342.6356             06/02/2009
2           67.9302               06/02/2009
3           1473.4946             06/02/2009
1           1518.724              06/03/2009
2           1574.3981             06/03/2009
3           689.1654              06/03/2009

And that’s all there is to it.  Using Paritition Over with Row_Number gives more flexibility in sorting similar information than just using Order By or Group By. 

Supporting Code:
CREATE TABLE [dbo].[Test](
 [TestId] [char](36) NULL CONSTRAINT [DF_Test_TestId]  DEFAULT (newid()),
 [Description] [varchar](50) NULL,
 [ClientId] [int] NULL,
 [Balance] [money] NULL,
 [SystemDateTime] [datetime] NULL CONSTRAINT [DF_Test_SystemDateTime]  DEFAULT (getdate())
) ON [PRIMARY]

GO
Declare @cash money
 ,@Count Int
 ,@LoopCount Int
 ,@CurrentDate datetime

Select @Count = 3
Set @LoopCount = 1
set @currentdate = '6/1/2009'

While @LoopCount <= @Count

Begin

--create 3 records for 3 days for client replace Client Id with 1, 2, 3
 Insert Into Test (ClientId, Balance, SystemDateTime)
 select 1 as ClientId
 , CAST(CAST(newid() AS binary(3)) AS money) as Balance
 , dateadd(s,  CAST(CAST(newid() AS binary(2)) AS int), @currentdate) as newdate

 Insert Into Test (ClientId, Balance, SystemDateTime)
 select 1 as ClientId
 , CAST(CAST(newid() AS binary(3)) AS money) as Balance
 , dateadd(s,  CAST(CAST(newid() AS binary(2)) AS int), @currentdate) as newdate

 Insert Into Test (ClientId, Balance, SystemDateTime)
 select 1 as ClientId
 , CAST(CAST(newid() AS binary(3)) AS money) as Balance
 , dateadd(s,  CAST(CAST(newid() AS binary(2)) AS int), @currentdate) as newdate

 set @currentdate = dateadd(d,  1, @currentdate)

           Set @LoopCount=@LoopCount + 1

End

RSS Feed http://www.damba.net

Flexible T-SQL:  Bending T-SQL to Get Around Everyday Programming Obstacles Part III

This blog post continues the presentation that will be given at the June 12, 2009 Macon .NET User’s group

A common problem when dealing with stored procedures in SQL Server concerns the passing of variable arrays.  The developer has a stored procedure that requires an array of integers, but there’s no straightforward was to get the array to the stored procedure as there’s no such thing as a array variable in SQL Server 2000 and 2005.  However, there is a workaround: pass XML from .NET to SQL Server.

There are two parts:  preparing the parameter array in .NET, and processing the array in T-SQL.

Let’s handle the first part.  We’re going to perform the following steps using VB.NET to prepare the XML parameter array:
1. Create a dataset in .NET
2. Add a datatable to the dataset
3. Apply ColumnMapping to the dataset
4. Use the getXML method of the dataset to get the XML

So, for Step #1, we’re going to create a new dataset and call id “FormIds”.  It’s very important to name the new dataset, otherwise your XML root tag will be “<NewDataSet></NewDataSet>”. 

****************** BEGIN VB.NET CODE SNIPPET **********************

Dim dtForm As New DataTable ‘The table that holds the array of FormIds
Dim ds As New DataSet("FormIds") ‘The Dataset that generates the XML
Dim dr As DataRow ‘A temporary row that adds each FormId to the table
Dim dt As DataTable ‘A result table that holds the result of the stored procedure called with the parameter array
Dim sql As String ‘the name of the stored procedure that needs the parameter array
Dim parameters As Hashtable ‘the parameters passed to the stored procedure

‘This table is our array and is arbitrarily called “datarow”
dtForm.TableName = "datarow"
‘the name of our variable
dtForm.Columns.Add("FormId")
‘additional columns can be added, each additional column will become an attribute within the xml

‘add variables to our array table
For formId as integer = 0 to 5
    dr = dtForm.NewRow
    dr("FormId") = formId
    dtForm.Rows.Add(dr)
Next

‘add our array table to our dataset
ds.Tables.Add(dtForm)

‘add ColumnMapping so that when the dataset is converted to XML, each column is an attribute
‘example XML row = <datarow FormId="0" />
For iLoop As Integer = 0 To ds.Tables.Count - 1
    For Each col As DataColumn In ds.Tables(iLoop).Columns
col.ColumnMapping = System.Data.MappingType.Attribute
    Next
Next

‘get XML from our dataset
sqlXML = ds.GetXml()

‘Here’s how the XML generated by the ds.GetXML will look:

‘<FormIds>
‘ <datarow FormId="0" />
‘ <datarow FormId="1" />
‘ <datarow FormId="2" />
‘ <datarow FormId="3" />
‘ <datarow FormId="4" />
‘ <datarow FormId="5" />
‘</FormIds>

‘FormIds is the name of the dataset, datarow is the name of the table, and FormId is the name of the column.

‘Call the Stored Procedure (see separate note about the GetDataTable function
sql = "GetFormsByFormXML"
parameters = New Hashtable
parameters.Add("@xml", sqlXML)

dt = GetDataTable(sql, parameters)

‘Side note – this is a helper function to easily add parameters to a SQL query
Public Shared Function GetDataTable(ByVal Sql As String, ByVal Parameters As Hashtable) As DataTable

        Dim oConnStr As String
        Dim param() As SqlClient.SqlParameter
        Dim ds As New DataSet
        Dim tableName As String
        Dim dt As DataTable
        Dim parameterValue As String
        Dim keyCollection As ICollection

            tableName = Sql.Substring(3, Sql.Length - 3)
            ‘stub
            oConnStr = GetConnectionString()
            param = SqlHelperParameterCache.GetSpParameterSet(oConnStr, Sql, True)
            keyCollection = Parameters.Keys

            For Each keyname As String In keyCollection
                parameterValue = Convert.ToString(Parameters(keyname))
                For Each parm As SqlClient.SqlParameter In param
                    Select Case parm.ParameterName.ToLower
                        Case keyname.ToLower
                            parm.Value = parameterValue
                    End Select
                Next
            Next
            SqlHelper.FillDataset(oConnStr, CommandType.StoredProcedure, Sql, ds, New String() {tableName}, param)
            dt = ds.Tables(tableName)
            ds.Tables.Remove(dt.TableName)
            Return dt
    End Function

****************** END VB.NET CODE SNIPPET **********************

Now for the the stored procedure that will receive the XML.  We’re going to use OpenXML to parse the XML into a table variable, and then link that table variable with a query to yield the results we were looking for.  To use OpenXML, perform the following steps:
1. Declare a table variable to hold the array that results from the XML
2. Declare a dochandler variable as an integer
3. Call sp_xml_preparedocument on the XML
4. Select the columns from the XML and place them into the table variable
5. Call sp_xml_removedocument on the XML to destroy it.

For this example, it is presumed that there is a table in SQL server called [Form] that has two columns, [FormId] and [Description].

****************** BEGIN T-SQL CODE SNIPPET **********************

-- Note:  this is a snipped of code that would become the stored procedure GetFormsByFormXML referenced by the VB.NET code

declare @xml xml -- the incoming XML from VB.NET
-- note: in SQL Server 2000, this variable will have to be either TEXT
-- or a varchar large enough to hold all possible xml values

--example XML
set @xml = '<FormIds>
  <datarow FormId="0" />
  <datarow FormId="1" />
  <datarow FormId="2" />
  <datarow FormId="3" />
  <datarow FormId="4" />
  <datarow FormId="5" />
</FormIds>'

declare @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML

declare  @FormIds  table(
 FormId char(36)
)

insert into @FormIds
Select FormId
FROM OPENXML(@DocHandle, '/FormIds/datarow',1)
WITH (FormId  char(36))

exec sp_xml_removedocument @DocHandle

select f.FormId
, f.Description
from form f
 inner join @FormIds fi
  on fpd.formid = fi.formid

****************** END T-SQL CODE SNIPPET **********************

And that concludes our example.  We have created an array in VB.NET, passed that array to T-SQL, and successfully used that array to filter our result set.  This procedure should help solve some of the common coding obstacles that we run into on a regular basis.

RSS Source:  http://www.damba.net

Flexible T-SQL:  Bending T-SQL to Get Around Everyday Programming Obstacles

My last blog post has evolved into a presentation on T-SQL that will be given in June at the Macon .NET User’s group.  The presentation will cover three parts: Using OpenXML to pass arrays to stored procedures, using Partion Over and Row_Number to narrow down queries, and using Bitwise Operators to generator more flexible queries.   This blog post continues the Bitwise Operator entry.

First, a quick note about Bitwise – it must be an integer.  That presents a limit of 32 different bitwise categories for SQL Server 2005.  That’s because the integer on SQL Server is 4 bytes, which yields 2^31.

We want to make an easy way to directly connect a Car table with the Bitwise Options table.  As noted in the first part of this post, Bitwise operators require specifying the categories: e.g. to find Option 8, the query must be:

Select CarId from Car where OptionId & 8 = 8. 

That’s fine, except this step must be repeated for each Option:

                Select CarId from Car where OptionId & 8 = 8 or OptionId & 16 = 16.

This itemization will get tedious after awhile, especially if the need is felt to create suboptions to extend the 32 option limitation.  Fortunately, there is a way around this by using a WHILE loop in T-SQL.

--This table stores the relevant bitwiseids

Declare @BitTable as Table

(

      BitCodeId int

)

--This section loops through the bitwiseids and places them in the stored procedure

DECLARE @bitMask INT

DECLARE @counter INT

BEGIN

      SET @bitMask = 1

      SET @counter = 1

      WHILE (@counter < 31)

      BEGIN

            IF ((@OptionId & @bitMask) = @bitMask)

            BEGIN

                  Insert Into @BitTable (BitCodeId) Values (@BitMask)              

            END

            SET @bitMask = @bitMask * 2

            SET @counter = @counter + 1

      END

      -- Hits here when the 31st bit needs to be checked. We do it here

      -- since if we multiply by 2, the number we get is 2147483648, which

      -- results in an overflow

      SET @bitMask = 2147483647

      IF ((@originalNumber & @bitMask) = @bitMask)

      BEGIN

                   Insert Into @BitTable (BitCodeId) Values (@BitMask)              

      END

END

So the While loop finds all the BitWiseIds that comprise the OptionId number and inserts them into a table.  So what we do then is Cross Join the OptionId on our Car table with our bitwise table and we get the options associated with that join.

Let’s refresh our example tables:

“Option” Table

 16          Cold Air Intake

“Car” table

CarId     Description  OptionId

1              Blue Car               5

2              Red Car               26

“Cross Joining Car Table with BitTable from T-SQL snipped, above.

SELECT Distinct c.CarId

, c.Description

, b.BitCodeId

FROM Car c

Cross join @BitTable b

where  b.BitCodeId & c.OptionId = b.bitcodeid

Yields:

CarId     Description         OptionId

1              Blue Car               1

1              Blue Car               4

2              Red Car                2

2              Red Car                8

2              Red Car               16

So now we have a concise and effective way of applying multiple options to a table using a single, flexible bitwise number.

RSS Source:  www.damba.net

This blog entry will be on an underutilized, but very effective, technique in database programming called the Bitwise Operator.

The bitwise operation specifies that each entry has a value that increments in power of 2 steps: 1, 2, 4, 8, 16, etc.

Why is this useful?  Well, it is extremely useful in being able to find particular combinations of options without having to hard-code all options into a table.  For example, let's say that you are a car manufacturer and you offer the following options:  cloth, leather, 6-cylinder, 8-cylinder.  One method of designing the database would be to have an Options table that looks like this:

A Lookup table would then be required to see what items were on each car, as follows:

All well and good.  The problem occurs when more options are added, such as wood trim, catback exhaust, and 11-speaker surround sound system and the client wishes a report on how many 6-cylinder cars were sold with leather and a catback exhaust, and then mentions that the next report needs to include the cars color on it as well.  The reports can be done, however they will become time consuming and more complicated as options are added. 

The solution is the Bitwise operator.  Let's change our above table to bitwise and see how it simplifies adding new options and searching for them.

In bitwise operations, the OptionIds are added together to get the relevant option group.  For example, CarId 132 has an OptionId of 5, which translates to a car with Cloth (1) and 6-cylinders (4).  Meanwhile, CarId #135 with an OptionId of 24 has 8-Cylinders (8) and a Cold Air Intake (16).

The T-SQL to make this search looks like the following: 

OptionId & OptionValue = Result

To search for a list of cars that have 8 cylinders and a Cold Air Intake, the SQL query would be:

Select OptionId from Car Where OptionId &  8 = 8 and OptionId & 16 = 16.

This system allows for easily adding on new options and search criteria without having to recode the project.