Dec 06

MS SQL Server List to Table Function

I need this for work real quick, and hopefully I’ll get some time to update the content later on.

This User Defined Function will turn a comma separated list (or any single character, for that matter), and turn it into a table so you can join on it. My recommendation is to shove it into a table variable first, rather than join on the function. It may be an old wives tale, but I’d rather do it this way once, than go through all of the procedures that use it to extract it out ūüôā

CREATE FUNCTION dbo.udf_ListToTable
(
	@Input VARCHAR(8000),
	@Delimiter VARCHAR(1)
)
RETURNS @Return TABLE (ID INT IDENTITY, [Value] VARCHAR(1000))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    DECLARE @String VARCHAR(1000)

    WHILE LEN(@Input) > 0
    BEGIN
        SET @String = LEFT(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input) - 1, -1),
			LEN(@Input)))
        SET @Input = SUBSTRING(@Input,
			ISNULL(NULLIF(CHARINDEX(@Delimiter, @Input), 0),
			LEN(@Input)) + 1, LEN(@Input))

        INSERT INTO @Return ([Value])
			VALUES (@String)
    END

    RETURN
END

To use, do the following:

DECLARE @Fruit TABLE (ID INT, Fruit VARCHAR(50))
INSERT INTO @Fruit
	SELECT	ID, [Value]
	FROM	dbo.udf_ListToTable('Apple,Banana,Kiwi,Strawberry', ',')
Posted in SQL
Sep 17

Connection Strings

Every time I want to create a new project, I either have to look into previous projects, or go to ConnectionStrings.com. It’s a wonderful site that you can use to find the specific connection string that you may ever need.

For me, however, I typically use 1 of 2 connection strings. It either has a username/password, or it uses integrated security (currently logged in user).

SQL Server 2008 Connection String with User name and Password:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQL Server 2008 Connection String with Integrated Security:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Aug 29

MS SQL Server Convert DateTime to String

I regularly use a SQL statement to extract a datetime, as I feel like it formats it faster than code. Plus, if you update the source, you only have to do it once, as opposed to doing it in every place that it gets displayed.

Here is the conversion number to use in the CONVERT statement.

CONVERT(VARCHAR(30), [DateValue], [Style])
Style Output
0 Sep 11 2001  8:52AM
1 09/11/01
2 01.09.11
3 11/09/01
4 11.09.01
5 11-09-01
6 11 Sep 01
7 Sep 11, 01
8 08:52:21
9 Sep 11 2001  8:52:21:000AM
10 09-11-01
11 01/09/11
12 010911
13 11 Sep 2001 08:52:21:000
14 08:52:21:000
20 2001-09-11 08:52:21
21 2001-09-11 08:52:21.000
22 09/11/01  8:52:21 AM
23 2001-09-11
24 08:52:21
25 2001-09-11 08:52:21.000
100 Sep 11 2001  8:52AM
101 09/11/2001
102 2001.09.11
103 11/09/2001
104 11.09.2001
105 11-09-2001
106 11 Sep 2001
107 Sep 11, 2001
108 08:52:21
109 Sep 11 2001  8:52:21:000AM
110 09-11-2001
111 2001/09/11
112 20010911
113 11 Sep 2001 08:52:21:000
114 08:52:21:000
120 2001-09-11 08:52:21
121 2001-09-11 08:52:21.000
126 2001-09-11T08:52:21
127 2001-09-11T08:52:21
130 23 ????? ??????? 1422  8:52:21
131 23/06/1422  8:52:21:000AM

Also, if you happened to notice the date: Yes, it is the time that the first plane hit the first world trade center… Never Forget.

I’m still coding, so we are winning!

You can also view more details on the MSDN site here.

Posted in SQL
Jun 22

Fun Code Friday – SQL Binary Generator

I was daydreaming about some SQL code the other day, and wrote a note to myself to investigate.

I’ve always been interested in writing loops to output sequential data. I don’t know why, I just do.

So, here is some SQL that will generate some binary.

DECLARE @b TABLE (c VARCHAR(1))
INSERT INTO @b VALUES (0)
INSERT INTO @b VALUES (1)

SELECT	b1.c + b2.c + b3.c + b4.c + b5.c + b6.c + b7.c + b8.c
FROM	@b b1, @b b2, @b b3, @b b4, @b b5, @b b6, @b b7, @b b8
ORDER BY 1
Jun 04

SQL Server Case Sensitive Queries

Recently, I’ve been tasked to create a data mask function.

They want:

  • A number will be a number
  • An upper case letter will be upper case
  • A lower case letter will be lower case
  • Punctuation and symbols will remain the same

An example would be:

123-00-6789 would become 456-88-4321
Mark would become Trxz

To enhance obfuscation, it will only be used one-way, and a 1:1 character swap is not necessary. Therefore, an uppercase A could be an upper case Z and an upper case X in the same sentence.

Thanks to this article at Novick Software, I was able to come up with the following example for future use.


--Create a table variable
DECLARE @Tbl TABLE (Name VARCHAR(100))

--Insert same name with different case
INSERT INTO @Tbl VALUES ('mark')
INSERT INTO @Tbl VALUES ('Mark')

–Gets the 1st (case-insensitive) result
SELECT DISTINCT Name FROM @Tbl

–Gets case-sensitive distinct results
SELECT DISTINCT Name COLLATE sql_latin1_general_cp1_cs_as FROM @Tbl

–Gets the distinct count (case-insensitive)
SELECT COUNT(DISTINCT Name) FROM @Tbl

–Gets the distinct count (case-sensitive)
SELECT COUNT(DISTINCT NAME COLLATE sql_latin1_general_cp1_cs_as) FROM @Tbl

Posted in SQL
Jul 11

.NET Connection Strings – To Encrypt or Not to Encrypt

Recently I was tasked with researching connection string best practices, and employing them at the day job. We want a better way to manage all of the connection strings.

I suggested moving the connection strings into the machine.config unencrypted. This will accomplish multiple things.

  • Centralizes connection strings to one area for all projects residing on that server
  • Easy to search, maintain, and update since no encryption/decryption is required
  • “Set it and Forget it” – Deployments to the various environments (DEV, STG, TRN, PRD) won’t need to update connection string after every deployment cycle

But there are some downsides

  • Need to save across multiple .NET machine.config sources
    The simple workaround is to keep a configSource file in the root, and utilize a batch file to copy into each frameworks config directory (except 1.1 and below)
  • Unencrypted connection strings means an intruder has easier access to information (more info below)
  • Updating a connection string for one project has the potential of disrupting every project on that server
    But I hope you are doing these updates during off hours, so the impact will be minimal.

I will make some mentions of things in this blog that I really hope is not news to anyone. Having your own 2-way hashing/encrypting algorithm will only slow down a hacker. My experience has been that you must “hide” the key, but still reference it. A good file search tool can quickly point to this unlock key to get your connection string. By my estimation, the tool I use can sift through 80 megabytes of data (including text files, images, and binary documents such as PDFs, Word and Excel documents) in less than 8 minutes. Then using a decompiler they can easily get your information.

I was once tasked to take over a project where the previous developer was holding the source code ransom for a bigger bonus. With the awesome experience in programming I had developed over the years, it took me about 45 minutes to get everything I needed into a nice small package. And that was just so I had something pretty to work with, not just mining for anything specific.

I’m sure that if a somewhat experience hacker ever got into your machine, an encrypted connection string is only going to slow him down by maybe 15 minutes, assuming he already has the tools necessary to do his digging. After all, it’s all about the time. If he’s already gained access, knows what he’s looking for, and wasn’t taught the Boy Scout model of “Be Prepared” (even if it’s to be used for evil), the chances of him being caught will be much higher as he’s doing some Google research while breaking into your system.

So, my recommendation is to just keep unencrypted connection strings in the machine.config file(s), as it will save your Operations department many compounding hours in maintenance over the lifetime of the servers.

Mar 02

Simple SQL Pivot with Example

A colleague was interested in solving a reporting problem for our business users.  And as many times go, I ask him what he wants the end result to be, so that I can figure out how I would tackle the problem.  He explains that he wants the following:

License # #1 #2 #3
0001 2000 500
0002 500
0003 200 100

This table represents a Personal Identifier (Drivers License ID, Business ID, etc), followed by fine amounts (the lower number being the most recent fine).  Other examples will use column headers of other known values such as product lines (Apples, Oranges, etc), or dates (Jan 2011, Feb 2011, etc) as summation values.

Let’s use the following Table Variables as our data model:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

To me, it seemed that a “Pivot” table was in order.¬† Based on past experience, I knew that I needed to get the data in the following format, before doing the pivot…

License # Ticket Row Amount
0001 1 2000
0001 2 500
0002 1 500
0003 1 200
0003 2 100

To get the data like this, we would use the following SQL:

SELECT	P.LicenseNumber,
		ROW_NUMBER() OVER(PARTITION BY T.PersonID
			ORDER BY T.TicketDate DESC)
			TicketRowNumber,
		T.TicketAmount
FROM	@Person P
	LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
ORDER BY 1, 2

Simple enough, right? We use the ROW_NUMBER with PARTITION BY to segment we want to reset the count each time a new PersonID is encountered. This will get us our column headers for the Pivot table.

Now, we just add in the Pivot call, and we can get our data as we want it. All together, that SQL will look like:

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
	) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Some things to note: This does not handle dynamic columns. There are other examples out there that can cover this, so I will wait until I have a need to create another post for it.

And here is all of the SQL that will also generate some test data:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

INSERT INTO @Person VALUES ('0001')
INSERT INTO @Person VALUES ('0002')
INSERT INTO @Person VALUES ('0003')
INSERT INTO @Person VALUES ('0004')
INSERT INTO @Person VALUES ('0005')

INSERT INTO @Ticket VALUES (1, '1/1/2011', 500)
INSERT INTO @Ticket VALUES (3, '1/4/2011', 300)
INSERT INTO @Ticket VALUES (2, '1/5/2011', 200)
INSERT INTO @Ticket VALUES (1, '1/10/2011', 600)
INSERT INTO @Ticket VALUES (1, '2/1/2011', 2000)
INSERT INTO @Ticket VALUES (5, '3/1/2011', 250)

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
		) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Enjoy!

Posted in SQL