Search - Joel Lipman .Com

1. Zoho Deluge: Sort a Map by a specific field: / Zoho /; ... date format), arrange so it can be sorted alphabetically in reverse order v_DatePart = v_SortingKey.getPrefix("T").toDate().toString("yyyy-MM-dd"); v_TimePart = v_SortingKey.getSuffix("T").getPrefix("+").subString(0,5); v_SqlDateTime ...; Created on 29 January 2021
2. Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date String: / Zoho /; ... = v_XeroTime.indexOf("(")+1; v_EndIndex = v_XeroTime.indexOf("+", v_StartIndex); v_UnixSeconds = v_XeroTime.subString(v_StartIndex, v_EndIndex); info v_UnixSeconds.toLong().toTime(); // yields 15-Apr-2020 ...; Created on 04 May 2020
3. MySQL: Count occurrences of words in a column: / MySQL /; ... as `total`, `value` FROM ( SELECT count(*) AS `total_count`, REPLACE(REPLACE(REPLACE(x.`value`,'?',''),'.',''),'!','') as `value` FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(t.`introtext`, ...; Created on 12 March 2020
4. Zoho Deluge: Convert Map to URL Parameters: / Zoho /; ... = v_ReturnedUrl.subString(v_ReturnedUrl.indexOf("?")+1).toList("&"); m_UrlParams = Map(); for each r_Part in l_ReturnedUrlParts { if(r_Part.contains("=")) { l_ParamParts = r_Part.toList("="); m_UrlParams.put(l_ParamParts.get(0), ...; Created on 09 March 2020
5. Zoho CRM/Creator - Common Errors & Gotchas: / Zoho /; ... v_TimeZone = zoho.currentdate.toString("Z"); v_TimeZoneStr = v_TimeZone.substring(0,3) + ":" + v_TimeZone.substring(3); v_InsertDateTime = zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss") + v_TimeZoneStr; // ...; Created on 06 February 2020
6. Zoho Deluge - Compare time and currenttime with timezone: / Zoho /; ... HH:mm:ss"); v_ExpiryTime = "2020-03-25T21:41:07+00:00"; v_ExpiryDatePart = v_ExpiryTime.subString(0,10); v_ExpiryTimePart = v_ExpiryTime.subString(11,19); v_ExpiryTimeCheck = v_ExpiryDatePart + " " ...; Created on 13 January 2020
7. Zoho Deluge - Generate 5 Letter Booking Retrieval Code: / Zoho /; ... return v_Output; } This is my preferred method and it will return a string of 5 random uppercase letters. Method #2: Convert time to letters I've previously used substring to extract the ...; Created on 09 May 2019
8. Zoho Deluge - Get English Ordinal: / Zoho /; ... 2, 3, ... 29, 30, 31) This_Day_Date = My_Date_Field.toString("d"); // determine English Ordinal Ordinals = "th,st,nd,rd".toList(); English_Ordinal = Ordinals.get(0); EO_Index = This_Day_Date.substring(This_Day_Date.length() ...; Created on 08 November 2018
9. Migrating from Joomla 2.5.x to 3.4.x: / Joomla /; ... AND t1.`extension`='com_content' AND t2.`extension`='com_content' SET c1.`catid`=t1.`id`; -- Joe-Code: LIST Rows to be affected (for test purposes) -- SELECT SUBSTRING(a.`name`, 1, INSTR(a.`name`, ...; Created on 12 September 2015
10. Android Java: Remove first two entries from Google News feed in SimpleRssReader: / AndroidOS /; ... } holder.newsItemTitle.setText(items.get(position).getTitle()); holder.newsItemDateD.setText(items.get(position).getDate().substring(5, 7)); holder.newsItemDateM.setText(items.get(position).getDate().substring(8, ...; Created on 24 October 2014
11. T-SQL functions to convert Strings to Tables: / Transact-SQL /; ... ', @StringInput) - 1, -1), LEN(@StringInput))) SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(' ', @StringInput), ...; Created on 20 August 2014
12. Add attribute xsl:nil=true on empty elements using XSLT: / XML Stylesheet Language Transformations /; What? An article on how to declare an XML element as NULL using the attribute "xsi:nil". I'm going to use a very short example by providing a blank date of birth value: -- What I have: // -- ...; Created on 18 August 2014
13. SITS: Export field code and name: / Extensible Markup Language /; Applies to: Microsoft Windows 7 Enterprise SITS:Vision Students (v8.7.0) What? You might be able to work it out from the online manuals but it took me a while, we wanted to bring back both the ...; Created on 12 August 2014
14. Convert to Proper Case in T-SQL: / Transact-SQL /; ... -- get the delimited word SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i SET @CaseValue = SUBSTRING(@CaseExceptions, ...; Created on 04 June 2014
15. SSIS Script: convert UPPERCASE to Mixed-Case using TitleCase: / SQL Server Integration Services /; ... in exceptionArray_this) { if (ValueToConvert.Contains(myValue)) { string McValue = ValueToConvert.Substring(0, ...; Created on 04 October 2013
16. Split a row into multiple rows based on a column value: / Transact-SQL /; ... EmployeeNo , DaysOffSick , DateOfSickness ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items FROM Employees_Attendance_Table JOIN master..spt_values ...; Created on 23 August 2013
17. SSIS Skip Blank Rows in Flat File Source: / SQL Server Integration Services /; ... is not null and data column is not blank. Derived Column Task: Format keycolumn as per your requirements and use substring on trimmed DataColumn to retrieve each data column. Additional ...; Created on 23 April 2013
18. SSIS Convert a string into a date: / SQL Server Integration Services /; ... giving up with a "Data Conversion" task. I then parse the date, month and year out using SUBSTRING: -- Assuming [This_Date]="21/03/2012" (DT_DATE)(This_Date) -- Assuming [This_Date]="20120321" ...; Created on 14 March 2013
19. Quickly update all content in mySQL database replacing a string: / MySQL /; ... @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew, SUBSTRING(`content_column`, LOCATE( @stringtoFind, `content_column` )) as OldFind, REPLACE(`content_column`, @stringtoFind, ...; Created on 03 February 2013
20. Cheat Sheet for mySQL vs t-SQL: / Databases /; ... haystack) CHARINDEX(needle, haystack [, offset]) String Concatenation CONCAT(string1,string2[,stringN]) string1 + string2 SS v2012+: CONCAT(string1, string2 [, stringN ]) String Substring SUBSTRING( ...; Created on 31 January 2013