Many of us manage databases or applications that contain regulated data that can’t leave a production environment. This means that we need to “clean” the data if it’s going to be used in QA or development work and one common way to de-identify the data is to simply update columns like firstname and lastname with a simple format “firstname” + counter; however, this results in all the data being unique and sequential. Firstname1, firstname2, firstname3, …
This isn’t good for getting like for like results with a production database and can lead to questions we’ve heard before in the workplace like “Why didn’t we catch this in QA?”

Dawn of AI

We have so many new tools at our fingers with prompt AI like ChatGPT being able to give suggestions on email formatting and content, research detail on Azure or other topics, creating stories or even poems. I asked myself, “Can ChatGPT generate data for SQL Server (and other databases)?”

The answer is yes; but, it comes with limitations. Let’s discuss…

Generating Realistic Test Data with AI

At first, I questioned if ChatGPT could create test data for a table schema at all, since it uses a generic model that isn’t specific to SQL code. I prompted “Create test data for my table with the following schema: create table x (id int, firstname varchar(20), lastname varchar(20), lastpurchasedate datetime2(0))” and the result was surprisingly good!!

Next I wondered how fancy I could get and asked for some of the values in date columns to be null. Yet again I was quite pleased with the result.

Knowing that the AI can generate test data I figured, why not create 5000 rows that I could dump into my table and get straight to testing. Let’s push this thing and see what we get. The result was not quite what I wanted. In fact it produced code that creates data with the exact issue described in the opening paragraphs of this post. Non-realistic, sequential test data is not what we’re after.

Why not prompt it to create “realistic data”?

OK This code is good but there’s a big problem with it. It doesn’t actually generate any test data it assumes there is a firstname and lastname table to pull values from. But wait, We can use this code if we generate smaller batches of names using the prompts, right? Let’s see.  How many names can we generate at one time? The answer is 50. I tried 5000, 100, and then 50. Anything after 50 will result in 50 names and a comment saying “Add more data here”, lol. We can work with this though!

Prompt ChatGPT to “generate 50 realistic first names not based on a counter”.

We can use the strength of this AI to give us many diverse names to use by prompting different countries or regions like “US, France, Germany” or “American, French, German, …” names. Just remember that the real limit is 50. You can even prompt the AI to give you 50 names that are not in a list of names, giving you new names to work with. So if you want 100 Australian names, prompt it for the first 50 then prompt it for another 50 but ask it not to include the names in the original 50 you just got.

Now that we have a few hundred first and last names we can use the code we got earlier to generate test data for our application dev and qa work.

Here’s the simple code I came up with. Keep in mind you can use the first names and last names that you generate to randomly assign data in your “de-identification” process as well. This code is just a very simple example to get you thinking of the possibilities.

— random data generation
SET NOCOUNT ON;

— Create a temporary table for German first names
CREATE TABLE #FirstNames (firstname varchar(20));

— Enable random data generation
SET NOCOUNT ON;

— Insert 50 random German first names
INSERT INTO #FirstNames (firstname)
VALUES
(‘Maximilian’), (‘Sophie’), (‘Lukas’), (‘Mia’), (‘Finn’),
(‘Lena’), (‘Leon’), (‘Emma’), (‘Luca’), (‘Hannah’),
(‘Benjamin’), (‘Marie’), (‘David’), (‘Anna’), (‘Elias’),
(‘Laura’), (‘Tim’), (‘Lea’), (‘Paul’), (‘Johanna’),
(‘Julian’), (‘Emilia’), (‘Jan’), (‘Sophia’), (‘Noah’),
(‘Amelie’), (‘Felix’), (‘Mila’), (‘Simon’), (‘Clara’),
(‘Moritz’), (‘Elena’), (‘Jonas’), (‘Victoria’), (‘Oskar’),
(‘Mara’), (‘Nico’), (‘Isabella’), (‘Erik’), (‘Maria’),
(‘Julius’), (‘Fiona’), (‘Tom’), (‘Maja’), (‘Anton’),
(‘Sofia’), (‘Vincent’), (‘Emily’), (‘Jakob’);

— Insert 50 random French first names into the existing temporary table #lastnames
INSERT INTO #FirstNames (firstname)
VALUES
(‘Léa’), (‘Lucas’), (‘Chloé’), (‘Louis’), (‘Emma’),
(‘Gabriel’), (‘Manon’), (‘Jules’), (‘Inès’), (‘Léo’),
(‘Camille’), (‘Hugo’), (‘Sarah’), (‘Arthur’), (‘Alice’),
(‘Raphaël’), (‘Zoé’), (‘Enzo’), (‘Louise’), (‘Nathan’),
(‘Eva’), (‘Théo’), (‘Clara’), (‘Maël’), (‘Charlotte’),
(‘Tom’), (‘Lola’), (‘Antoine’), (‘Julia’), (‘Mathis’),
(‘Lea’), (‘Paul’), (‘Mia’), (‘Nathan’), (‘Emma’),
(‘Alexandre’), (‘Lina’), (‘Matheo’), (‘Lou’), (‘Ethan’),
(‘Jade’), (‘Evan’), (‘Léna’), (‘Noah’), (‘Lilou’),
(‘Louis’), (‘Elena’), (‘Sacha’), (‘Mila’), (‘Leo’);

— Insert 50 random American first names into the existing temporary table #lastnames
INSERT INTO #firstNames (firstname)
VALUES
(‘Emma’), (‘Liam’), (‘Olivia’), (‘Noah’), (‘Ava’),
(‘Isabella’), (‘Sophia’), (‘Jackson’), (‘Lucas’), (‘Mia’),
(‘Oliver’), (‘Ethan’), (‘Aiden’), (‘Harper’), (‘Evelyn’),
(‘Abigail’), (‘Emily’), (‘Amelia’), (‘Benjamin’), (‘Ella’),
(‘Alexander’), (‘Henry’), (‘Jacob’), (‘Michael’), (‘Daniel’),
(‘William’), (‘James’), (‘Elijah’), (‘Avery’), (‘Sofia’),
(‘Grace’), (‘Scarlett’), (‘Jack’), (‘Chloe’), (‘Lily’),
(‘Luna’), (‘Zoe’), (‘Mila’), (‘Stella’), (‘Aria’),
(‘Eleanor’), (‘Hazel’), (‘Violet’), (‘Leo’), (‘Aurora’),
(‘Levi’), (‘Lucy’), (‘Nora’), (‘Ethan’), (‘Ava’);

— Select the generated German first names
SELECT * FROM #FirstNames;

— Create a temporary table for German last names
CREATE TABLE #LastNames (lastname varchar(20));

— Insert 50 random German last names
INSERT INTO #LastNames (lastname)
VALUES
(‘Schmidt’), (‘Müller’), (‘Schneider’), (‘Fischer’), (‘Weber’),
(‘Meyer’), (‘Wagner’), (‘Becker’), (‘Schulz’), (‘Hoffmann’),
(‘Koch’), (‘Bauer’), (‘Richter’), (‘Wolf’), (‘Schröder’),
(‘Schwarz’), (‘Hartmann’), (‘Lange’), (‘Schmitt’), (‘Werner’),
(‘Schmitz’), (‘Krause’), (‘Maier’), (‘Lehmann’), (‘Herrmann’),
(‘Köhler’), (‘Klein’), (‘Walter’), (‘Schuster’), (‘Böhm’),
(‘Seidel’), (‘Lorenz’), (‘Schwarz’), (‘Bergmann’), (‘Engel’),
(‘Vogel’), (‘Hahn’), (‘Kramer’), (‘Frank’), (‘Sommer’),
(‘Kühn’), (‘Barth’), (‘Zimmermann’), (‘Graf’), (‘Peters’),
(‘Krüger’), (‘Jäger’), (‘Gross’), (‘Voigt’);

— Insert 50 random French last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Martin’), (‘Bernard’), (‘Dubois’), (‘Thomas’), (‘Robert’),
(‘Richard’), (‘Petit’), (‘Durand’), (‘Leroy’), (‘Moreau’),
(‘Simon’), (‘Laurent’), (‘Lefevre’), (‘Michel’), (‘Garcia’),
(‘David’), (‘Bertrand’), (‘Roux’), (‘Vincent’), (‘Fournier’),
(‘Morel’), (‘Girard’), (‘Andre’), (‘Lefevre’), (‘Mercier’),
(‘Dupont’), (‘Lambert’), (‘Boucher’), (‘Colin’), (‘Caron’),
(‘Renaud’), (‘Picard’), (‘Lemoine’), (‘Lecomte’), (‘Leger’),
(‘Dufour’), (‘Perrin’), (‘Marchand’), (‘Leclerc’), (‘Begue’),
(‘Gauthier’), (‘Faure’), (‘Rousseau’), (‘Blanc’), (‘Guerin’),
(‘Bouvier’), (‘Riviere’), (‘Chevalier’), (‘Beaumont’), (‘Lemaire’);

— Insert 50 random American last names into the existing temporary table #lastnames
INSERT INTO #lastnames (lastname)
VALUES
(‘Smith’), (‘Johnson’), (‘Williams’), (‘Jones’), (‘Brown’),
(‘Davis’), (‘Miller’), (‘Wilson’), (‘Moore’), (‘Taylor’),
(‘Anderson’), (‘Thomas’), (‘Jackson’), (‘White’), (‘Harris’),
(‘Martin’), (‘Thompson’), (‘Garcia’), (‘Martinez’), (‘Robinson’),
(‘Clark’), (‘Rodriguez’), (‘Lewis’), (‘Lee’), (‘Walker’),
(‘Hall’), (‘Allen’), (‘Young’), (‘Hernandez’), (‘King’),
(‘Wright’), (‘Lopez’), (‘Hill’), (‘Scott’), (‘Green’),
(‘Adams’), (‘Baker’), (‘Gonzalez’), (‘Nelson’), (‘Carter’),
(‘Mitchell’), (‘Perez’), (‘Roberts’), (‘Turner’), (‘Phillips’),
(‘Campbell’), (‘Parker’), (‘Evans’), (‘Edwards’), (‘Collins’),
(‘Stewart’);

— Select the generated German last names
SELECT * FROM #LastNames;

— Enable random data generation
SET NOCOUNT ON;

create table #x (id int identity(1,1), firstname varchar(25), lastname varchar(25), lastpurchasedate datetime2(3))

— Declare variables
DECLARE @Counter INT = 1;

— Loop to insert 5000 rows
WHILE @Counter <= 5000
BEGIN
INSERT INTO #x (firstname, lastname, lastpurchasedate)
VALUES
(
(SELECT TOP 1 firstname FROM #FirstNames ORDER BY NEWID()),
(SELECT TOP 1 lastname FROM #LastNames ORDER BY NEWID()),
CASE WHEN @Counter % 5 = 0 THEN NULL
ELSE DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % (365*3) /*3 years*/, GETDATE()) END);

— Increment counter
SET @Counter = @Counter + 1;
END;

select * from #x

In one of my next posts, I hope to use Azure’s Open AI resource to implement this concept further. Keep an eye out for that post.

SQL Server

SQL Server’s substring function is really straight forward and works just like you’d think. Pass in a string or column, tell it where to start, give it a number of characters you want to return.

Example:

SELECT SUBSTRING(‘The quick brown fox.‘, 5, 5)

Result:

quick

Substring isn’t it though; SQL Server has a few tricks up it’s sleeve, LEFT() & RIGHT(). These two functions take either the left N number of characters or the right N number of characters, giving more options for would be SQL coders.

Oracle

If you’re coming from a SQL Server and have worked with SUBSTRING quire a bit then you may feel you’ve already mastered the SUBSTR() function in Oracle; but, you haven’t.

Example:

SELECT SUBSTR(‘The quick brown fox.‘, 5, 5) FROM dual;

Result:

quick

Looks the same as SQL Server, right? Well, Oracle is has something cool up it’s sleeve just as well. Here’s something that SQL Server can’t do with SUBSTRING.

Example:

SELECT SUBSTR(‘The quick brown fox.‘, -3, 3) FROM dual;

Result:

fox

That’s right, Oracle’s SUBSTR function can take a negative value. The only catch is that you can’t go past the length of the string. In this case the length of this string is 20. So if you did –20, the result would be nothing. –19 would start at the T in The. Hopefully that didn’t confuse you too much. My recommendation would be to download Oracle and give it a try.

Conclusion

Parsing strings is a feature that is often needed in the database world and SUBSTRING/SUBSTR are designed to do just that. I find it interesting how these two platforms approached the functions differently and that’s definitely shows how you can do many things to get to the same answer.

I honestly can’t say which I prefer. What do you all think?

Comparing a column with NULL and replacing with another value is really simple. There are built in functions for replacing NULL values. I’m not going to discuss the ANSI standard COALESCE here. If you want to know more about it you can find it on Bing.

SQL Server

SQL Server’s built in function for replacing NULL values is named ISNULL(). This very simple function takes two parameters, tests the first for NULL and if NULL is found substitutes the second parameter for the first.

SELECT ProductID, Weight, ISNULL(Weight, 0.00)
FROM Production.Product

Result

1, NULL, 0.00

2, NULL, 0.00

…

SELECT ISNULL(NULL, ‘XYZ’)

Result

XYZ

With the ISNULL function you can pass NULL as the first parameter; though, I’m not sure why you would want to.

SQL Server 2012 added the IIF function which works just like the MS Office IIF. Though I don’t see this widely used. Typically you’ll see ISNULL and COALESCE.

Oracle

The Oracle function may not be the one you’d think of first. Oracle has a function named NULLIF and it does something completely different. NULLIF compares to values and if they are the same returns NULL.

Replacing NULL values in Oracle is done with the NVL function, which works the same as SQL Server’s ISNULL described above.

Pass a column and if the column is NULL the function will return the value of the second parameter.

It’s worth mentioning that Oracle also has a function named NVL2 which allows you to pass in 3 parameters. The first parameter is compared and if NULL then the 3rd parameter is returned and if not NULL then the 2nd parameter is returned.

Conclusion

Both RDBMS platforms have the ANSI standard function for comparing and replacing NULL values (COALESCE). Both platforms have two native functions for this task that behave in nearly the same manner. So if you’re switching between the two or learning one or the other, this isn’t an area you need to worry about.

Finishing out the week  I’d like to return to dates and discuss how each platform can create a date from a string. This sounds easy but it’s not as simple as it sounds, well at least not for SQL Server.

SQL Server

There are many ways to create a date from a string. First you’ve got the CONVERT() function and the CAST() function. Next you’ve got DATEFROMPARTS(), DATETIMEFROMPARTS(), DATETIME2FROMPARTS(), SMALLDATETIMEFROMPARTS(), TIMEFROMPARTS(), and DATETIMEOFFSETFROMPARTS().

That’s a lot of functions for one simple task isn’t it? To be fair, it’s really more than 1 simple task. Each of these functions is meant to be paired with the matching data type so you get just what you want. To go along with these you also have the ISDATE() function which tests the value to be sure it’s a date.

CONVERT() is straight forward but there’s a catch. Let’s say you want to convert a string of “MMDDYY” to date.

SELECT CONVERT(date, ‘070903‘)

Result:

2007-09-03

This is the result you get weather you use the format parameter or not. So now you’re stuck parsing this and passing the values to one of the FROMPARTS functions to build your date. This is where Oracle shines.

Oracle

The TO_DATE() function in Oracle allows a date format to be passed, making date conversion really easy. Let’s see it in action.

SELECT to_date(‘070903’, ‘mmddyy’) FROM dual;

Result:

09-JUL-03

Since we can pass a date format the types of strings we can pass to the TO_DATE() functions are near endless. How about ‘HHMMMIDDSSYYYY’?

SELECT to_date(‘02073909532003’, ‘mmddyy’) FROM dual;

Result:

09-JUL-03

Clearly you’d be placed on some sort of medication if you were storing your dates in HHMMMIDDSSYYYY string format but the greatness that comes out of this is that no matter the format you can convert to a date as long as you know the pattern.

Conclusion

While SQL Server gives you a shopping cart full of functions for converting strings to date and various other date flavored data types, it still doesn’t muster up to Oracle’s TO_DATE() function. Something tells me I should write some sample TO_DATE() code for SQL Server and perhaps that’s what I’ll do.

If you enjoyed this post and would like to read more check out my blog: SQLTechBlog.com. You can also find more at Twitter, LinkedIn, and Instagram.

Thankfully, someone smart came up with a way to do this on the database so you’re not returning gigs and gigs of data to the webserver to sort through.

SQL Server

Pagination in SQL Server prior to SQL Server 2012 was a pain. Thankfully the OFFSET clause was introduced with 2012 and now pagination is a breeze. Let’s examine the code:

SELECT AddressID, City, PostalCode
FROM Person.Address
ORDER BY AddressID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

An ORDER BY clause must be specified to use OFFSET. You can choose to Fetch next [n] or not. Specifying the FETCH NEXT [n] will limit the rows returned to [n] while using OFFSET without a FETCH NEXT will only omit the first [n] rows your offset by. In this case 10 rows at the front would be ignored.

Oracle

Paging in Oracle uses the ROWNUM function. ROWNUM is used for other tasks like getting the top [n] rows but can easily be used for pagination as well. Let’s examine the code:

SELECT table_name FROM (SELECT table_name, rownum rnum FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 20) WHERE rnum > 10;

To use rownum for pagination you’ll want to nest derived tables to get your result. This can easily be used by an application to programmatically get the rows you want.

Conclusion

In my opinion, the OFFSET FETCH feature of SQL Server is much easier to use; however, it took Microsoft a very long time to add this to SQL Server and rownum has existed in Oracle for more than 20 years. No matter which you’re using both have the same outcome and I hope this article helps you understand the differences between the two platforms.

You can find more of my posts at my blog: SQLTechBlog.com. If you like the information you found here, please follow me at LinkedIn, Twitter, and Instagram.

SQL Server

There are a couple different ways to get the top rows in SQL Server. You can either do this by [n] or by [%]. You can choose to use an order by or not. If no ORDER BY clause is used the rows will be based on the tables default sort order.

SELECT TOP 10 AddressId FROM Person.Address

OR

SELECT TOP 1 PERCENT AddressId FROM Person.Address

Oracle

Top seems easy enough to use and you’d think it’s part of the ANSI standard for SQL but it’s not. TOP is not included in Oracle’s language; instead, there’s ROWNUM.

If you’re coming from SQL Server, ROWNUM doesn’t work you may expect. You can’t just replace TOP with ROWNUM. Another catch is that ROWNUM is assigned before the ORDER BY so you may want a derived table to get the top rows with a sort.

SELECT table_name FROM sys.all_tables WHERE rownum <= 25;

OR for a sorted list

SELECT table_name FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 25;

Conclusion

Simple isn’t always better. I like SQL Server’s TOP operator but it’s only going to give you the top number or percent. To get the next 5 or next [n] you’ll have to use OFFSET clause that was introduced in SQL Server 2012. This is called Pagination and is a very important feature for web pages or applications that want to paginate results.

Getting the next [n] in Oracle is a breeze with ROWNUM and has been around since probably the beginning of time before even the white walkers existed. To paginate with ROWNUM you simply use two derived tables like the following example:

SELECT table_name FROM (SELECT table_name, rownum rnum FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 20) WHERE rnum > 10;

You can find more of my posts at my blog: SQLTechBlog.com. If you like the information you found here, please follow me at LinkedIn, Twitter, and Instagram.

End of Month

Getting the end of the month is a really important task for any report writer or developer that needs to write code to close out or query data by month.

Beginning with SQL Server 2012 you can easily obtain the last day of the month with the EOMONTH() function. Prior to this you needed to do some magic with DATEADD() and while this worked and was efficient it always seemed like a lacking.

The last day of the month can be retrieved in Oracle with, you guessed it: LAST_DAY(). This function has been around since Oracle 8i which came out in 1998.

Usage

SQL Server:

SELECT EOMONTH(GETDATE())

RESULT:

2017-08-31

Oracle:

SELECT last_day(sysdate) FROM dual;

Result:

31-AUG-17

I hope you enjoyed this simple post today. If you’d like to read more of my posts they can be found on my blog at SQLTechBlog.com. You can also find more from me on LinkedIn, Twitter, and Instagram.

Both of these systems use SQL, which is a language used to access and modify data in databases. SQL or Structured Query Language has been an ANSI and ISO standard since the mid 1980’s.

Since SQL itself is a standard, why is this post needed? Well as it turns out, each database vendor placed their own flavors of SQL in their software. For Oracle this may be PL/SQL and SQL Server it’s T-SQL. These additions help enhance the database performance over that of the ANSI standard language. An example of this is the ISNULL function.

Obtaining the current date and time in SQL Server

Querying dates is a breeze in SQL Server but can be done in many ways. To get the current date simply query the GETDATE() function. This will return the current time with accuracy of .000, .003, or .007 seconds and is based on the DATETIME data type. Using SYSDATETIME() results in accuracy of 100 nanoseconds because it’s based on the DATETIME2 data type.

These aren’t the only two functions for getting the current time. Actually there are quite a few more that deal with time zones and other such items but that’s a topic for another post. Today we’ll focus on just these two.

Example:

SELECT GETDATE(), SYSDATETIME()

Result:

2017-08-04 14:05:25.620    2017-08-04 14:05:25.6221444

Obtaining the current date and time in Oracle

Getting the current date from Oracle is just as easy but takes a tiny bit more. Rather than query a function directly you’ll want to query from a table. In this case we’ll query the DUAL table.

To query the current date and time, you’ll want to use the SYSDATE and SYSTIMESTAMP functions. There’s one catch. SYSDATE returns only the date but it has the time if you need it. Sounds complex but let’s look at it in action.

SELECT SYSDATE, SYSTIMESTAMP FROM dual;

Result:

04-AUG-17    04-AUG-17 02.19.05.880000 PM –05:00

Quite different results than that of SQL Server.

Formatting date and time values in SQL Server

Formatting comes in many forms in SQL Server. You can format dates using the CONVERT() function. You can also format using the FORMAT() function. Format was added in SQL Server 2012 and is structured very similar to MS Office.

FORMAT()

This is a really neat function because it allows you to do so much so easily. You can format as a culture such as ‘en-us’ or ‘de-de’. This will show the date and time as whatever is preferred in that culture.

SELECT FORMAT(getdate(), ‘d’, ‘en-gb’)

Result:

31/07/2017

You can also format by using the Office type strings such as ‘dd/MM/yyyy’ or ‘MM/dd/yy hh:mm.ss’

SELECT  FORMAT(getdate(), ‘MM/dd/yy hh:mm.ss’)

CONVERT()

Convert is great but it isn’t as easy. Since when is the “classic” method ever easy?  Convert has plenty of functions. Formatting datetime values is just a small fraction of the things it can do.

A full list of formats can be found here: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

I’d say the one I’ve used the most over the years is 112. This is because I would generally use this in a BCP file name or something similar. 108 may also be useful for file names.

SELECT CONVERT(varchar(8), GETDATE(), 112)

Result:

20170731

Formatting date and time values in Oracle

One way to format date and time values in Oracle is to use the to_char() function. This function is similar to SQL Server’s convert() function but focuses on changing values to a character string.

SELECT to_char(sysdate, ‘MM/DD/YY HH24:MI:SS’) FROM dual;

Result:

07/31/17 14:37:04

Conclusion

Even though both Oracle and SQL Server support ANSI and ISO standards, performing certain tasks is different. Mastering these differences should be a breeze if you’ve been around databases or even a spreadsheet application long enough. It’s just a matter of simple syntax in many occasions.

If you enjoyed this article and would like to read more of my articles, please take a moment to look over my blog at SQLTechBlog.com.

If you’d like to connect on LinkedIn, I can be found here: LinkedIn.com.

I can also be found on Twitter and if you like food, fishing, and outdoor life: Instagram.