Cheat Sheet for mySQL vs t-SQLhttps://www.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 Month Number MONTH(now()) DATEPART(mm, getdate()) European Date (dd/mm/yyyy) DATE_FORMAT(now(), '%d/%m/%Y') CONVERT(varchar, getdate(), 103) Time to Seconds TIME_TO_SEC(now()) (DATEPART(hour, getdate()) * 3600) + (DATEPART(minute, getdate()) * 60) +...

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

SSRS: Performance Improvements: SELECT TOPhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-performance-improvements-select-top.html

07 March 2014 LIKE @GivenForenames + '%' OR @GivenForenames = '') AND (Surname LIKE @GivenSurname OR @GivenSurname = '') AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB OR @GivenDOB = '') AND (@GivenRef'' OR @GivenForenames'' OR @GivenSurname'' OR @GivenDOB'') -- The...

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

Strip HTML in MySQLhttps://www.joellipman.com/articles/database/mysql/strip-html-in-mysql.html

06 December 2011 log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS fnStripTags; DELIMITER | CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) RETURNS varchar(4000) DETERMINISTIC BEGIN DECLARE iStart, iEnd, iLength int; WHILE Locate( '', Dirty, Locate( '',...

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

Upgrading Joomla CMS version 3.x to Joomla CMS 5.xhttps://www.joellipman.com/articles/cms/joomla/upgrading-joomla-cms-version-3-x-to-joomla-cms-5-x.html

04 February 2025 the insert(s) into the new table `#_categories` where `id` > 7 Add column in #_content just before note called `xreference` VARCHAR(50) allows NULL. Import Content (articles): export SQL file and amend the insert(s) into the new table `#_content` Import...

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

MySQL Data Type Reference Tablehttps://www.joellipman.com/articles/database/mysql/mysql-data-type-reference-table.html

15 February 2010 Numeric Decimal Varies Maximum width + 2 bytes String Char Range 1-255 characters Always filled max width String VarChar Range 1-255 characters Length of string + 1 byte String TinyBlob, TinyText Max length 255 characters Length of string + 1 byte...

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

SSRS Querying on either of 2 Parametershttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-querying-on-either-of-2-parameters.html

19 July 2011 must be able to search on EITHER the student's username or the student's ID (2 report parameters: @StudentADAccount [varchar] and @StudentReference [int] respectively). Most of the remaining datasets use the resulting @StudentReference number in their...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• 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 text DEFAULT ''; -- return string holder DECLARE temp_str text DEFAULT ''; -- temporary string holder DECLARE temp_val VARCHAR(255) DEFAULT ''; -- temporary string holder for query -- get length SET str_len=LENGTH(str); SET i =...

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

CharIndex Reverse - find occurrence starting from end of string in TSQLhttps://www.joellipman.com/articles/database/t-sql/charindex-reverse-find-occurrence-starting-from-end-of-string-in-tsql.html

28 January 2013 we should determine the position of the last needle first (reverse the haystack string and find needle): DECLARE @Haystack VARCHAR(31); SET @Haystack = 'String1.String2.String3.String4'; -- I want "String4" from Haystack SET @Delimiter = '.'; PRINT...

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

T-SQL: Parse an XML valuehttps://www.joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 the opening and closing XML tags: -- Using .query() SELECT CAST(CAST(Event_XML AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender FROM [dbo].[XML_EVENTS] -- Returns column GENDER for every record/row (eg. "Male") Or -- Using .value() SELECT...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• 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 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 (select true from...

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

Convert a delimited string to tablehttps://www.joellipman.com/articles/database/convert-a-delimited-string-to-table.html

26 October 2012 and just leaves VALUE. Why? Do we need a reason? How? For SQL Server 2005 or later, using T-SQL: DECLARE @myStringToParse VARCHAR(max), @myXML XML; SET @myStringToParse = 'Title,Forenames,Surname'; SELECT @myXml = CONVERT(xml,'' +...

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