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

28 January 2013 Not quite right, as we got the last occurrence counting from the beginning rather than the end, so we still need to work out the starting point: SELECT LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)) -- yields "23" SELECT SUBSTRING(@Haystack,...

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

SSIS Convert a string into a datehttps://joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 will have a string of 8 characters long in the format DDMMYYYY and on other rows, this is blank with spaces instead, I still get the Error: The conditional operation failed. -- I've been given data as a Flat File with fixed columns (Ragged Right). --...

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

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

04 April 2013 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 descriptions to the column,...

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

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

06 April 2013 Framework conflicts) You select what words are highlighted what color. Uses separate CSS file for overall look. This is still in development! Currently this plugin is mostly hard-coded and most of the parameters have no effect. It was initially built...

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

MS Excel - Sort pivottable column headings by datehttps://joellipman.com/articles/microsoft/excel/sort-pivottable-column-headings-by-date.html

12 April 2013 tell me that "10" comes before "8" and it does but just to check, let me pad the 8 with the prefixing zero: Annoyingly "10" still comes before "08" so maybe I'm barking up the wrong tree. If I padded with one more zero so that "008" is before "010":...

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

Improve Default Joomla Search https://joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 search, but the above files are enough to start adding your own code to make the "relevance" ordering more relevant.... ok still to come as I'm really happy with what the above already improves! Download the above modified files. The following ZIP file...

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

T-SQL concatenate an incremental row numberhttps://joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 There! So we're using the ROW_NUMBER() functionality with an ORDER BY which seems to work somewhat. But using the code above still produces an error which can only be seen when using more records and more employees. PARTITION BY So a sort of "group"...

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

Generate a Timesheet in MySQL https://joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 to reconcile with the requirement example. Note that we have two events on the Thursday. A few things we have to deal with still: We have lost the rows with days that have no events. We need to group by days so that each day only outputs one row (see...

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

Hide a Drive per User in Windows 7https://joellipman.com/articles/microsoft/windows-os/hide-a-drive-per-user-in-windows-7.html

28 March 2014 the registry editor, then restart the computer. Notes: Guest account will not see the drive in Windows Explorer but can still access it using the command prompt. All users can access the drive using the command prompt. To hide from specific users, you...

• Type: Article
• Author: Joel Lipman
• Category: Windows OS
• Language: *

DJI Phantom FC40 Paint-Job and AerialFreaks Cyclops FC40 Gimbal Upgradehttps://joellipman.com/articles/_other-misc/quadcopters/dji-phantom-fc40-paint-job-and-aerialfreaks-cyclops-fc40-gimbal-upgrade.html

04 September 2014 masked these before painting as per youtube videos) Possibly my new facebook profile photo My old FC40 looking new(ish) Ok, still going with painting on a budget, yes those are permanent marker color-ins the chipped smaller propeller is stock Less on a...

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

Can't play music after Firmware Update Version 3.0https://joellipman.com/hardware/cant-play-music-after-firmware-update-version-30.html

21 September 2009 multi-audio output then you can play music again. What a bummer you can't play music and use anything else anymore. I can still play some games and select my own music... but they're dumbass PSN games that iPhone users would have got for a pound or what...

• Type: Article
• Author: Joel Lipman
• Category: IT Support
• Language: *

Basic Android App using Google Maps and Current Locationhttps://joellipman.com/articles/google/androidos/basic-android-app-using-google-maps-and-current-location.html

10 October 2014 Just in case: AndroidManifest.xml The example above should have populated this correctly but if you are still getting problems, I have the following fields that I also use related to Google Maps v2: Last Additional Ensure that when Google asks for your...

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

Quadcopter App with Flight Restrictions (JQA)https://joellipman.com/component/content/article/quadcopter-app.html?catid=40

24 October 2014 unmanned aerial surveillance system (less than 20kg equipped with a camera). Regulations different to the UK and US are still being reviewed prior to being added to the database but each country's aviation authority has been added. Earthquake Feed for...

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

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

25 November 2014 Note that this article is based on a new blank project which does not include code from the previous example but you can still build this over the previous example (as I did). Why? The previous article meant that the Google map was your main activity...

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

Warning: Save failed. Another menu item has the same aliashttps://joellipman.com/articles/cms/joomla/warning-save-failed-another-menu-item-has-the-same-alias.html

13 March 2015 we would want entitled "about" or "about-us" but these were already created along the way and then deleted. The system is still storing the offending menu items somewhere. How? The fix that I use involves a change to #__menu table in the database for...

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

Convert XML UTF-16 to JSON UTF-8 with PHP cURLhttps://joellipman.com/articles/web-development/php/convert-xml-utf-16-to-json-utf-8-with-php-curl.html

28 March 2015 curl_setopt($ch, CURLOPT_CERTINFO, TRUE); curl_setopt($ch, CURL_SSLVERSION_SSLv3, TRUE); Future Considerations The data still hasn't been properly decoded from UTF-16 and encoded to UTF-8 Test writing to a file, re-encoding the file then reading from...

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

Migrating from Joomla 2.5.x to 3.4.xhttps://joellipman.com/articles/cms/joomla/migrating-from-joomla-2-5-x-to-3-4-x.html

12 September 2015 1, INSTR(a.`name`, ':|joe')-1 ) WHERE a.`name` LIKE '%:|joe|:%'; Weblinks As helpful as they'll ever be. Still, I have a few I might want to keep, might as well migrate all of them: --...

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

Google Drive API v3 - OAuth2 using Service Account in PHP/JWThttps://joellipman.com/articles/google/google-drive-oauth-using-service-account-in-php.html

02 February 2019 = floor($diff/60); } 6a. Use existing Access Token This if else statement simply says if the token still has at least 5 minutes left, then use the one found at the file location specified earlier. if( $api['jwt']['token']['minutes'] > 5){ $access_token...

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

Zoho Templates - Font size is inexplicably tinyhttps://joellipman.com/articles/crm/zoho/zoho-templates-font-size-is-inexplicably-tiny.html

22 February 2019 by an issue which I'd been working on for most of the day where I would remove all the styling and the text would still appear small. Sure I resized the text to about 46pt and everything was over 3000px in width but this is not good for a final...

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

Zoho Deluge - Get Refresh/Access Token API v2https://joellipman.com/articles/crm/zoho/zoho-deluge-get-refresh-access-token.html

17 July 2019 $v_AccessToken; $a_Header[1] = "Content-Type: application/x-www-form-urlencoded;charset=UTF-8"; If you are using the SDK and still get the above error, try setting the currentUserEmail to the email address of the super admin of your system just to...

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