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 positions added for demo purposes): String1.String2.String3.String4 1 5 10 15 20 25 30 -> length = 31 I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of...

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

Access MySQL databases using Oracle SQL Developerhttps://www.joellipman.com/articles/database/mysql/access-mysql-databases-using-oracle-sql-developer.html

01 February 2013 to use mostly SQL to create tables and stuff. Recommend to your bosses that you need the MySQL Workbench as this is FREE and with all the features you'd expect a GUI administration kit like this to have.

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

SSIS Skip Rows in Excel Source filehttps://www.joellipman.com/articles/microsoft/ssis/ssis-skip-rows-in-excel-source-file.html

04 April 2013 source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range. How? My example is that I have an excel sheet where the first 10 rows are...

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

Joes Syntax Styler (JSS)https://www.joellipman.com/component/content/article/joes-syntax-styler-jss.html?catid=40

06 April 2013 problems for the sites I wanted to use it on. Mainly because of JavaScript frameworks (like jQuery and MooTools) conflicting with other scripts but do I really need a complex solution? So here I am making my own again. I wanted something really basic...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 realised then the equals sign ("=") could be changed to a "LIKE" which will mean both values are expected to be a string. So with this in mind: SELECT r1.MapIn ( SELECT (COUNT(e2.EmpType)-1) AS MyCount FROM DMExtractEmployeeTable e2 LEFT JOIN...

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

Split a row into multiple rows based on a column valuehttps://www.joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 When migrating to a new system, the destination wanted 1 row per day. This meant that if in the old system, there was 1 row with an employee who took 2 days off, we would want 2 rows for that date for the same employee in the new system. What we have:...

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

htaccess Rewrites SEF URL and submits to PHPhttps://www.joellipman.com/articles/web-development/php/htaccess-rewrites-sef-url-and-submits-to-php.html

05 September 2013 (%) or apostrophes (*) then you are asking for trouble. Note my redirect for errors. Split the first string "myFolder" with the slash (/) as a delimiter, controlling the syntax/format of your site URLs. For Example...

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

Call to a member function mark() on a non-object in Joomla CMShttps://www.joellipman.com/articles/cms/joomla/call-to-a-member-function-mark-on-a-non-object.html

17 November 2013 Our last file backup was over a year old but we only needed the core files, a working system, and then we could proceed with a standard Joomla update. I suspected that part of an auto-update process had corrupted all the files. In our case, the problem...

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

Export Joomla Users scripthttps://www.joellipman.com/articles/cms/joomla/export-joomla-users-script.html

28 February 2014 I could just type a query on the database and get the same result, but this script was more fun. Simply create a text file with the following code and upload it to the root of your Joomla website. Access the page from a browser and you should get all...

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

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

07 March 2014 the problem? How? Left to its own devices, a search for "SMITH" as a surname would make the report spin for about 10 minutes without the possibility to cancel (link not working!?) and eventually would display circa 3000 matching results. If I attempted...

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

301 Redirect using htaccess filehttps://www.joellipman.com/articles/web-development/301-redirect-using-htaccess-file.html

03 April 2014 removed it. Not sure why nobody believed me when I raised the alarm but hey-ho. How? I'm going to show you how to do this with a .htaccess file: Upload a text file to the root folder of the old domain's website Rename it as ".htaccess" (or modify the...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• 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 This one has two sections, the first part capitalizes any letter after a non-alpha letter. The remainder of it is to deal with exceptions to the rule, words that you want in a specific case. CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) RETURNS...

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

Include a carriage return in a column headinghttps://www.joellipman.com/articles/microsoft/sql-server/include-a-carriage-return-in-a-column-heading.html

07 July 2014 will only understand the carriage return and often return it as "AcademicWeek"). Other Search(es) Format a column alias with new line Using carriage return in column names New line Character in column alias

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

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connecthttps://www.joellipman.com/articles/database/mysql/mysqldump-got-error-2049-connection-using-old-pre-4-1-1-authentication-protocol-refused-client-option-secure-auth-enabled-when-trying-to-connect.html

28 July 2014 on the MySQL Forums. Someone solved their issue by resetting (or re-issuing) their password which implies there was an issue with pre 4.1.1 passwords and the hashing algorithm: SET session old_passwords = 0; SET PASSWORD for 'youruser' =...

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

Batch Process to rename multiple files using Windows DOShttps://www.joellipman.com/articles/automation/ms-dos/batch-process-to-rename-multiple-files-using-windows-dos.html

09 August 2014 prompt and a bit of string manipulation. This example will rename files which contain the string " (Copy)" and replace it with nothing (so removes it). The challenge here is the space character and delimiting by a string. The Gist -- What I have...

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

DJI Phantom FC40 - Fun at the raceshttps://www.joellipman.com/articles/_other-misc/quadcopters/dji-phantom-fc40-fun-at-the-races.html

17 September 2014 could have taken (NTS: Take picture of whole track beforehand and plan a route). Strong Winds: Fighting the wind to keep up with the cars. Keep as much space as possible between our marshall. Not Near People: I could not risk flying low so I took a...

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

Android Java: Remove first two entries from Google News feed in SimpleRssReaderhttps://www.joellipman.com/articles/google/androidos/android-java-remove-first-two-entries-from-google-news-feed-in-simplerssreader.html

24 October 2014 first two entries from a news feed from a Google News feed. Modifying the strings can be done in the PcWorldRssParser.java (without it crashing the app). The gist is to check for a description (the first two items do not have one). The following will...

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

Excel: convert degrees minutes seconds to decimalhttps://www.joellipman.com/articles/microsoft/excel/excel-convert-degrees-minutes-seconds-to-decimal.html

06 November 2014 column N put the formula: =IF(J1="E", L1, L1 * -1) There we go! Column M has your latitude and Column N has your longitude. With the example above, this should return: A B C D E F G H I J K L M N --------------------------------------...

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

Android OS: Add GoogleMap as fragmenthttps://www.joellipman.com/articles/google/androidos/android-os-add-googlemap-as-fragment.html

25 November 2014 homepage of the app (the starting page) will simply display a button to the map (in case of offline mode or incompatibility with the device). How? In this example, I'm going to add the map as a fragment. This is the most rudimentary example (display...

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

Zoho Deluge - Get English Ordinalhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-get-english-ordinal.html

08 November 2018 This is a very quick note with the code to determine the English ordinal of a date (eg. "st" of "1st"). So in a date, instead of "Tuesday, 6 November 2018", I could want "Tuesday 6th of November 2018". Why? Well there's a long a way to do it (but...

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