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 sake, I'm assigning this string to the variable "haystack". How? Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle): DECLARE @Haystack VARCHAR(31); SET @Haystack =...

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

Country Lookup by IP address CSVhttps://www.joellipman.com/articles/database/mysql/country-lookup-by-ip-address.html

17 March 2013 more accuracy. Feel free to feedback. It's FREE to download unlike others which is probably why I made the generator in the first place but I do like to make people sign up so I can gauge the level of interest.

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

Parse a HTML Table into a ListViewhttps://www.joellipman.com/articles/web-development/html/parse-a-html-table-into-a-listview.html

30 March 2013 } LV_ModifyCol() Method #2 This method only uses two columns with the label being a concatenation of the value of the first column and the column heading. StringReplace,ReturnedHTMLTableRows,ReturnedHTMLTable,,|,A...

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

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

12 April 2013 in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top. Why? The excel report would hit a bug where it couldn't work out that 10...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• 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 the dates increment and do not account for days off (eg. Saturday / Sunday) How? Adapted from a forum topic: SQLTeam Forums First of all, we want the rows to multiply based on the value of the column: SELECT EmployeeNo , DaysOffSick , DateOfSickness...

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

Regular Expression Basic Usage Exampleshttps://www.joellipman.com/articles/web-development/regular-expression-basic-usage-examples.html

28 October 2013 a list. For example, to find either 'a', 'b', or 'c' use the following regular expression: [abc] This expression matches the first character in each of the following strings: at bet cot The expression does not match: def Non-Matching Character List Use...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Excel: Find values in one column that are not in anotherhttps://www.joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 Be careful with this one because if you copy the formula down, it may automatically modify the range. So the formula in the first row will be =COUNTIF(A2:A100, B2) but the formula in the second row will be =COUNTIF(A3:A101, B3): =COUNTIF(A2:A100, B2) --...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• 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 live system to a service user account... Especially one used by the scripts to access the database-driven website. How? I'm first going to highlight 2 methods which I found on the web and can be applied if you work somewhere that doesn't care about...

• 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 to !newName! RENAME "!oldName!" "!newName!" ) This code will rename the files. I did not find a way to undo so just run the first code to double-check what you're about to do. Save and Close the notepad file Return to the command prompt and type...

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

Add attribute xsl:nil=true on empty elements using XSLThttps://www.joellipman.com/articles/web-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 the data in XML, we might as well stick with the technology and we're going to keep our XSLT with some slight modifications. First let's look at the XML output from SITS (this is a cut down version for demonstration purposes): 1234567 JOEL LIPMAN Two...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• 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 importing to a database where DECIMAL(10,7) ). Merging the lot If B1 contains the coordinates and there is a space after the first letter (N or S): // for latitude =IF(MID(B1,FIND("″",B1)+1, 1)="N", (MID(B1, 1, FIND("°", B1)-1) + (MID(B1,FIND("°",B1)+1,...

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

Joes Search Module (JSM)https://www.joellipman.com/component/content/article/joes-search-module-jsm.html?catid=40

09 March 2015 be able to use this search or see the restricted documents. I've googled far and wide but as usual, I find myself to be the first to address this. How? The module I have made is very basic and not in an MVC structure but it has been tested on Joomla...

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

Sort an associative array by values in Javascripthttps://www.joellipman.com/articles/web-development/js/sort-an-associative-array-by-values-in-javascript.html

29 September 2015 What? An article on how to quickly adapt an array code and sort by its values. Surprising how many examples are on the web and everyone saying you're doing it wrong... Which is true but quite unhelpful. The original code is not my own either but that's...

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

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

05 October 2015 // prevents JS error where results unmatched column_values = column_values || []; // ignore row if first item is blank (allows for blank excel lines) if(column_values[0] != ''){ // remove any/all double-quotes in this column value // found this method...

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

PHP Issue: simplexml_load_string parser error : Input is not proper UTF-8, indicate encoding !https://www.joellipman.com/articles/web-development/php/php-issue-simplexml-load-string-parser-error-input-is-not-proper-utf-8-indicate-encoding.html

02 August 2016 hand. Other things I tried but to no avail The solution above was as easy as that. Here are a number of other things I tried first: mysql_set_charset(): No iconv(): No htmlentities(): No preg_replace_callback(): No sxe(): No $xml =...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: *

Pure JS - Display Time Elapsed & Remaininghttps://www.joellipman.com/articles/web-development/js/pure-js-display-time-elapsed-remaining.html

30 October 2018 ago was the last token created. How? So almost pure JS. I'm working out the seconds remaining and seconds elapsed in PHP first then passing these as two variables to the below code but you could get your values from anywhere: On page load, the seconds...

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

Excel - Check a column for values found in another columnhttps://www.joellipman.com/articles/microsoft/excel/excel-check-a-column-for-values-found-in-another-column.html

14 February 2019 Returns true if the cell value is a number AND the cell data type is numeric. Search( is a case-insensitive search with the first parameter being the text to search for (can be a range) and the second the string to search (in this case a single cell...

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

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

22 February 2019 should be obvious, but for some reason the header and footer of my code set with CSS {position:fixed} won't display on the first/cover page if I wrap the code in a DIV with ID = container and have an image which covers them both... Which is an...

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

Zoho Deluge - Some Useful Regular Expressionshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-some-useful-regular-expressions.html

31 May 2019 a string of characters to a list: v_MyString = "12345"; l_StringValues = v_MyString.replaceAll("(?)",",",false).removeFirstOccurence(",").removeLastOccurence(",").toList(); // yields list of [1,2,3,4,5] // NB:...

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

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

17 July 2019 = Map(); m_CreateContactPerson.put("contact_id", "1234567890123456"); // the account ID in books m_CreateContactPerson.put("first_name", "Joel"); m_CreateContactPerson.put("last_name", "Lipman"); m_CreateContactPerson.put("email",...

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