What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?

What?
We have a datawarehouse and we want to be able to count all the records in any table of the database that match on a particular warehouse load. This is a column value where the column is called "WarehouseLoadKey" and the value we want to search on is "3" (the 3rd incremental load).

How?
The below stored procedure can be reduced to just a script as long as you declare and set the parameters after the BEGIN and extract the script from BEGIN to END (excluding the words BEGIN and END - avoids the need to create a stored procedure and saving it on a database):

What?
This is a quick note to show you how to convert a given comma delimited string into a database table:
copyraw
Given:   "Title,Forenames,Surname"

Return:
  ID     Value
  ------ ----------------
  1      Title
  2      Forenames
  3      Surname

Note the below example omits the ID column and just leaves VALUE.
  1.  Given:   "Title,Forenames,Surname" 
  2.   
  3.  Return: 
  4.    ID     Value 
  5.    ------ ---------------- 
  6.    1      Title 
  7.    2      Forenames 
  8.    3      Surname 
  9.   
  10.  Note the below example omits the ID column and just leaves VALUE. 

Why?
Do we need a reason?


How?
Category: Databases :: Article: 440

Why?
Data Consistency. I was tasked with finding variations of our default values. End-users were complaining that some default values get listed twice because their system was case-sensitive despite the collation of the SQL Server being case-insensitive.

What?
End-users said that they could see the options "Data Not Yet Available" as well as "Data not yet available". These are obviously the same values, so I should go through the database and change all variations to be typed the same way in the same case.

Wouldn't it be nice to search through your entire database for similar sounding values (so not just case), and in this example, find typos as well, eg. "Daat ont Ety Aviable".

How?

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

What?
I have a PHP script which behaves like PhpMyAdmin, in that it automatically lists a database structure and the fields to modify similar to an excel spreadsheet. Now without using PHP, I need standalone SQL scripts that can simply be used to search an entire database for a particular string of word(s).

I've posted my own as well as some others I've lifted from elsewhere, as they worked with my environment, to put them in one place on a website I've bookmarked (my personal site :c)


What?
This article describes a function that will return random data based on a given value. The function intends to determine the data type and return data that is absolutely irrelevant to the original data but the same length and the same type of data.

Why?
The joy of working with the plethora of applications out there result in me using MySQL, Oracle PL/SQL and Transact SQL in my day job. The equivalent functions have to be written up all in the name of "seamless integration"...

How?

What?
How to use the randomization features of Oracle PL/SQL.

Why?
I've been tasked with writing a stored procedure that scrambles data for developers working with some of our databases containing sensitive data.

How?
copyraw
SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;
  1.  SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL; 
Category: Oracle PL/SQL :: Article: 431

Why?
Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want to update a temporary table and not the original (source) table, I needed the following stored procedure (or part of).

What?
This will copy a given table into a temporary table all the while maintaining the structure and data.

Thinking inside of the box
I think everyone suggests the following (or at least the idea of):
copyraw
SELECT *
INTO #MyTempTable
FROM @GivenTable
  1.  SELECT * 
  2.  INTO #MyTempTable 
  3.  FROM @GivenTable 
If @GivenTable is a parameter then the above will simply return an error. Also, if it was this easy, I wouldn't need to post this note on my website.

Category: Transact-SQL :: Article: 424

What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.


This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling.

Why?
We want to scramble sensitive data that we send to suppliers for support or analysis. With inspiration from: "Obfuscating your SQL Server Data" by John Magnabosco but tweaked for our purposes. NOTE that the following has only ever been run on development environments and I would not recommend running this on a production system as I have not tested the performance and database load.

copyraw
Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           mStnoh iJh           1926-01-02
           2           lgreg BFdos          1969-03-14
           3           onrUest ehAr         1968-05-05
           4           otentre AhYe Usr     1964-08-08
  1.  Before: 
  2.             StudentID   StudentName          DateOfBirth 
  3.             ----------- -------------------- ------------- 
  4.             1           John Smith           1990-03-21 
  5.             2           Fred Bloggs          1988-11-02 
  6.             3           Another User         1985-07-11 
  7.             4           Yet Another User     1977-06-25 
  8.   
  9.         After: 
  10.             StudentID   StudentName          DateOfBirth 
  11.             ----------- -------------------- ------------- 
  12.             1           mStnoh iJh           1926-01-02 
  13.             2           lgreg BFdos          1969-03-14 
  14.             3           onrUest ehAr         1968-05-05 
  15.             4           otentre AhYe Usr     1964-08-08 

Category: Transact-SQL :: Article: 422

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

source   list   work   file   time   mysql   zoho   display   form   script   data   function   create   order   version   server   parameter   where   date   user   client   following   uploaded   google   field   first   system   name   license   need   windows   report   used   website   joomla   note   error   files   value   creator   database   find   added   deluge   page   would   using   table   case   code   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.