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

01 August 2012 datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen = DATALENGTH(@OrigVal); SET @CurrLen = @OrigLen; SET @LoopCt = 1; SET @DateVal = ''; -- 1900-01-01 SET @TimeVal = ''; -- 00:00:00.000...

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

Search a database with SOUNDEXhttp://mail.joellipman.com/articles/database/search-a-database-with-soundex.html

11 October 2012 'nvarchar', 'text', 'ntext') ** ** AND TABLE_SCHEMA='dbo' ** ** ** ** - For performance, return results based on string length which is 1 greater or lesser than the original ** ** string value: ** ** Automatic: ** ** @minStringLength and...

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

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

07 December 2012 AS varchar(10)) + ' ' + CAST(@TimeVal AS varchar(16)); ELSE SET @NewVal = ''; -- REDUCE THIS STRING TO THE SAME LENGTH AS SUBMITTED VALUE // not necessary for update -- SET @NewVal = SUBSTRING(LTRIM(RTRIM(CAST(@NewVal AS VARCHAR(30)))), 1,...

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

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

28 January 2013 such as the following (column 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...

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

SSIS Skip Blank Rows in Flat File Sourcehttp://mail.joellipman.com/articles/microsoft/ssis/ssis-skip-blank-rows-in-flat-file-source.html

23 April 2013 way I've seen that has worked in nearly all cases, is to use the "conditional split" task and test one of the columns for a length of greater than 1: Almost! A problem not covered by many sites was happening to me when I had blank rows in a flat file,...

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

XML Schema Referencehttp://mail.joellipman.com/articles/web-development/xml/xml-schema-reference.html

13 August 2014 values fractionDigits Specifies the maximum number of decimal places allowed. Must be equal to or greater than zero length Specifies the exact number of characters or list items allowed. Must be equal to or greater than zero maxExclusive Specifies the...

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

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

18 August 2014 to do really: Add the xmlns:xsi namespace in the xsl:stylesheet tag so that "xsi:nil=true" is valid. Test for a string length of greater than 0 (not blank) and change the attribute of the element so that it's tag displays "xsi:nil=true"....

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

Joes Quicklist Weblinks (JQW) Downloadshttp://mail.joellipman.com/component/content/article/joes-quicklist-weblinks-jqw-downloads.html?catid=92

07 September 2015 Hit Counter: no, alongside, popup, both - Parameter: Specify Group(s) which can refresh thumbnails - Parameter: Description Length - Parameter: Image Rounded Corners (latest browsers only) - Parameter: Image Border Color - Parameter: Image Border Color...

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

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

08 November 2018 Ordinals = "th,st,nd,rd".toList(); English_Ordinal = Ordinals.get(0); EO_Index = This_Day_Date.substring(This_Day_Date.length() - 1).toLong(); if((EO_Index == 1 || EO_Index == 2 || EO_Index == 3) && (This_Day_Date != "11" && This_Day_Date != "12" &&...

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

Zoho Deluge: Loop through 30 Minute Slotshttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-loop-through-list.html

30 November 2018 v_includeInPicklist = false; for each index timeSlot in hourList { timeSlotStr = timeSlot.toString(); if(timeSlotStr.length() zoho.currenttime && v_CheckTime1b >= v_OpeningTime.toTime() && v_CheckTime1b...

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

CSS Printing - Page Break Inside with Headers and Footershttp://mail.joellipman.com/articles/web-development/css/css-printing-page-break-inside-with-headers-and-footers.html

22 February 2019 (background image filling the page with a logo floating at the centre) and the next page has a table which is of variable length. Fine when the table was short and didn't have many rows. But the client will pick up the phone to you when the table has...

• Type: Article
• Author: Joel Lipman
• Category: Cascading Stylesheets
• Language: *

MySQL: Count occurrences of words in a columnhttp://mail.joellipman.com/articles/database/mysql/mysql-count-occurrences-of-words-in-a-column.html

12 March 2020 BY `total` DESC LIMIT 0,50; Additional Note(s): I have added a WHERE clause which omits words less than 5 characters in length, and some other words not to count. I have added the ORDER BY to give me the highest count first in descending order. I have...

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

AutoHotkey: Get Media Information and Displayhttp://mail.joellipman.com/articles/automation/autohotkey/autohotkey-get-media-information-and-display.html

07 August 2020 DF { If not FileExist( sFile:=A_LoopFileLongPath ) { Return } SplitPath, sFile, _FileExt, _Dir, _Ext, _File, _Drv If ( p[p.length()] = "xInfo" ) ; Last parameter is xInfo { p.Pop() ; Delete parameter fex.SetCapacity(11) ; Make room for Extra info...

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

Zoho Deluge: Generate a loop or list of any sizehttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-generate-a-list-of-any-size.html

21 October 2022 of our interviewees as well as apparently in one of the Zoho documentation manuals. It uses leftpad to create a string of a length, converts it to a list, then lets you generate an array or list of any size. Why? My use case here is to run a schedule...

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

Zoho Cliq: Integrate OpenAI and ChatGPT 3.5 Turbohttp://mail.joellipman.com/articles/crm/zoho/zoho-cliq-integrate-openai-and-chatgpt-3-5-turbo.html

13 February 2023 gpt-3.5-turbo (optimized for chat at 1/10th the cost of text-davinci-003) gpt-4 gpt-4-32k (up to 4x the context length) Source(s): OpenAI - Documentation - API Reference - Making Requests OpenAI Account - API keys OpenAI Documentation - Create chat...

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

ZohoCRM & ZohoWriter: Generate Rich-Text / HTML Email Signatures in CRMhttp://mail.joellipman.com/articles/crm/zoho/zohocrm-zohowriter-generate-rich-email-signatures-in-crm.html

05 December 2023 = ifnull(m_User.get("signature"),""); // // criteria, only update signatures which aren't done? //if(v_CurrentSignature.length()...

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

ZohoCRM Client Script: On Change of Dropdown: Subform Rewrite: REST Functionhttp://mail.joellipman.com/articles/crm/zoho/zohocrm-client-script-on-change-of-dropdown-subform-rewrite-rest-function.html

01 April 2025 if (v_InvoiceType == "Final Balance") { // now loop through the quoted items / line items for (i = 0; i...

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

ZohoCRM & Xero: Function to pull most recent invoiceshttp://mail.joellipman.com/articles/crm/zoho/zohocrm-xero-function-to-pull-most-recent-invoices.html

09 October 2025 v_CrmProductCodeSafe = zoho.encryption.urlEncode(v_CrmProductCode); v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName); v_CrmProductNameSafe = zoho.encryption.urlEncode(v_CrmProductName);...

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

ZohoCRM & Xero: Function to pull most recent quoteshttp://mail.joellipman.com/articles/crm/zoho/zohocrm-xero-function-to-pull-most-recent-quotes.html

13 October 2025 the product is referred to in Xero (eg. if no code or name, then use description) v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName); v_CrmProductNameSafe =...

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

Make bootstrap carousel responsive to swipehttp://mail.joellipman.com/articles/web-development/bootstrap/make-bootstrap-carousel-responsive-to-swipe.html

14 December 2016 t.fn.bcSwipe=function(e){ var n={threshold:50}; return e&&t.extend(n,e),this.each( function(){ function e(t){ 1==t.touches.length&&(u=t.touches[0].pageX,c=!0,this.addEventListener("touchmove",o,!1)) } function o(e){ if(c){ var...

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