DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 CREATE FUNCTION ufn_DataScramble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; DECLARE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 CREATE FUNCTION ufn_DataJumble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; -- for dates/times...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Search a database for a string (MySQL, T-SQL)https://www.joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 the collate). This example is expanded in a further article called "Search a database with soundex": /* USING A CURSOR */ DECLARE @SqlToExecute nvarchar(max); DECLARE @mySearchString varchar(50); SET @mySearchString = 'dnya'; DECLARE MyCursor CURSOR FOR...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

DataTumble - Randomize Data Rowshttps://www.joellipman.com/articles/database/t-sql/datatumble-randomize-data-rows.html

07 December 2012 BEGIN TRY BEGIN TRANSACTION A DECLARE @NumberLeft INT DECLARE @random INT DECLARE @CurrentID INT DECLARE @ScrambledID INT DECLARE @Data VARCHAR(MAX) DECLARE @UpdateData VARCHAR(MAX) DECLARE @Chosen BIT DECLARE @ChosenID INT DECLARE @SqlStatement...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

T-SQL functions to convert Strings to Tableshttps://www.joellipman.com/articles/database/t-sql/t-sql-functions-to-convert-strings-to-tables.html

20 August 2014 value ** Return Values : None *********************************************************************************/ BEGIN DECLARE @StringValue VARCHAR(10) SET @StringInput = RTRIM(LTRIM(@StringInput)) WHILE LEN(@StringInput) > 0 BEGIN SET @StringValue =...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Search a database with SOUNDEXhttps://www.joellipman.com/articles/database/search-a-database-with-soundex.html

11 October 2012 *****************************************************************************************************************/ BEGIN -- Declare some variables to use DECLARE @SqlToExecute nvarchar(max); DECLARE @FullSqlToExecute VARCHAR(max); DECLARE @myCounter...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Getting the mysql where in delimited string to workhttps://www.joellipman.com/articles/database/mysql/getting-the-mysql-where-in-delimited-string-to-work.html

02 January 2012 confess that the bulk of this work is not mine and comes from a clever function by Chris Stubben in the MySQL Forums: BEGIN DECLARE i INT DEFAULT 0; -- total number of delimiters DECLARE ctr INT DEFAULT 0; -- counter for the loop DECLARE str_len INT; --...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Copy a table with structure and data into a temporary tablehttps://www.joellipman.com/articles/database/t-sql/copy-a-table-with-structure-and-data-into-a-temporary-table.html

03 August 2012 -- Create Stored Procedure CREATE PROCEDURE dbo.usp_MakeTableTemp @SchemaTableName nvarchar(100) AS BEGIN -- Variables used DECLARE @ColName varchar(50); DECLARE @TableName varchar(50); DECLARE @TableDeclaration nvarchar(4000); DECLARE @SqlToExecute...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Convert to Proper Case in T-SQLhttps://www.joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 that you want in a specific case. CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN -- declare some variables DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; DECLARE @c VARCHAR(2); -- specify reset...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Search a database for a value and count matching rowshttps://www.joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 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 -...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Parameters not being used in report processinghttps://www.joellipman.com/articles/microsoft/ssrs/parameters-not-being-used-in-report-processing.html

07 January 2011 a week based on a given date and campus -- =============================================================================== DECLARE @GivenDate datetime, @GivenCampus varchar(100), @setId varchar(10), @weekNumber int, @siteId nvarchar(5), @baseDate...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Stored Procedure to List Distinct Values and Countshttps://www.joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 *****************************************************************************************************************/ BEGIN -- Declare some variables to use DECLARE @ColToProcess varchar(max), @SqlToExecute varchar(max), @myXml xml; -- Parse comma...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Room Availability Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/microsoft/ssrs/room-availability-calendar-in-business-intelligence-development-studio.html

23 March 2011 me 4 hours just to do the below... hopefully it will be quicker next time now that I noted it all down. The SQL Script: DECLARE @StartTime datetime, @EndTime datetime, @GivenDate datetime; SET DATEFORMAT dmy; SET @GivenDate = '23/03/2011'; --First slot...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

Returning Oracle Stored Procedure Resultset in SSRShttps://www.joellipman.com/articles/microsoft/ssrs/returning-oracle-stored-procedure-resultset-in-ssrs.html

19 January 2012 PROCEDURE sp_get_studentdetails_from_ad ( p_STUDENT_ADNAME IN varchar2, l_CURSOR OUT sys_refcursor ) IS BEGIN -- going to declare variables that are to be returned in SSRS but not -- mentioned in the above OUT variables: DECLARE p_STUDENT_REF number(8);...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Import Excel CSV file as JavaScript arrayhttps://www.joellipman.com/articles/microsoft/excel/import-excel-csv-file-as-javascript-array.html

05 October 2015 of rows // store each line of the CSV file into a JS array var my_csv_rows_array = my_csv_file_contents.split("\n"); // declare a blank array to store file contents var obj_temp = []; // loop through JS array using Array.prototype.forEach()...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

URL Alias uniqueness with PHP & MySQLhttps://www.joellipman.com/articles/web-development/php/url-alias-uniqueness-with-php-mysql.html

06 May 2019 IF EXISTS incrementUrlAlias; DELIMITER | CREATE TRIGGER incrementUrlAlias BEFORE INSERT ON my_table_name FOR EACH ROW BEGIN declare original_url varchar(255); declare url_counter int; set original_url = new.url_alias; set url_counter = 1; while exists...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Zoho Creator: Copy Subform to other Subformshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-copy-subform-to-other-subforms.html

17 November 2021 // declaring the row = .(); // assigning values for various subform fields in the row . = ; . = ; // declaring another row (declare as many rows as required) = .(); // assigning values for various subform fields in the row . = ; . = ; // declare a...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM: Get All eBay Active Listingshttps://www.joellipman.com/articles/crm/zoho/zohocrm-get-all-ebay-active-listings.html

01 March 2023 - GetMyeBaySelling Documentation: https://developer.ebay.com/devzone/xml/docs/reference/ebay/getmyebayselling.html */ // // declare v_Page = 1; v_PerPage = 100; l_Pages = List(); // // specify page to start from v_StartingPageIndex = 1; // // specify...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Deluge: Get All Orders from eBayhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-get-all-orders-from-ebay.html

29 March 2023 - eBay Errors by Number: https://developer.ebay.com/devzone/xml/docs/Reference/ebay/Errors/errormessages.htm */ // // declare v_Page = 1; v_PerPage = 100; l_Pages = List(); // // specify the maximum number of orders you think you have on eBay (check...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

SQL Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 comes from www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/ DECLARE @StartDate DATETIME, @EndDate DATETIME --First day of current month SET @StartDate = DATEADD(s,0,DATEADD(mm,...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB