Search - Joel Lipman .Com

1. T-SQL concatenate an incremental row number: / Transact-SQL /; ... QualificationRef 001 00101 001 00102 002 00201 -- Qualification Ref is a unique number using the -- Employee Number concatenated with a 2 digit sequence -- number ...; Created on 24 May 2013
2. MySQL Group_concat equivalent in T-SQL and Oracle: / Databases /; ... version because I find myself needing to group concatenate more often from another table then using data from the same table. MySQL SELECT ( SELECT GROUP_CONCAT(column_to_return_as_string) ...; Created on 28 November 2011
3. Zoho Deluge: Convert Hex to RGB: / Zoho /; What? A quick article to document a Zoho Deluge function converting a hexadecimal color reference to a Red Green Blue value (RGB). Why? It's likely that Zoho will avail their color picker at some ...; Created on 11 October 2024
4. Zoho Analytics: Determine profits from invoices and purchase orders: / Zoho /; ... vItm Line Count"))) AS "Invoice Line Total inc Tax", GROUP_CONCAT(DISTINCT t1."PO Ref") AS "PO Refs", AVG(if_null(t1."PO Item Cost", 0)) AS "PO Item Cost", SUM(if_null(t1."PO Item Shipping", 0)) ...; Created on 03 November 2022
5. Zoho Creator/Deluge: Calculating with Timezone Offset: / Zoho /; ... display confirmation text v_ResultInformation = ""; v_ResultInformation = v_ResultInformation.concat("Customer: " + input.Name + " "); v_ResultInformation = v_ResultInformation.concat("Phone: " + input.Phone_Number ...; Created on 07 December 2021
6. Zoho CRM: Mapping a Multi-User or Multi-Lookup field using Deluge: / Zoho /; ... - USE ARROW KEYS OR MOUSE): As you can see from the screenshot above, there are modules concatenated with one module an "X" and then the name of the linked module. A module called "Opportunities_X_Products" ...; Created on 28 April 2021
7. Convert Past Date to Time Ago in PHP: / Personal Home Page /; ... array(); // loop concatenating unit value to its label for($i=0;$i0 ? $a_UnitValues[$i].' '.$a_UnitLabels[$i] : ''; // grammar for plural/singular (should always be a positive number) ...; Created on 16 May 2019
8. URL Alias uniqueness with PHP & MySQL: / Personal Home Page /; ... set original_url = new.url_alias; set url_counter = 1; while exists (select true from my_table_name where url_alias = new.url_alias) do set new.url_alias = concat(original_url, '_', ...; Created on 06 May 2019
9. Migrating from Joomla 2.5.x to 3.4.x: / Joomla /; ... `title`, `rules`) SELECT @assetParentID, a.`lft`, a.`rgt`, a.`level`, CONCAT('com_content.article.', @nextAssetContentID:=@nextAssetContentID+1, ':|joe|:', c.`id`) AS new_name, a.`title`, ...; Created on 12 September 2015
10. 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
11. T-SQL Conversion failed when converting the varchar to data type int: / Transact-SQL /; ... silly T-SQL concatenation which requires plus signs and thinks it's super clever when it tries to add a number and a string together; then remembers it's a Microsoft by-product and realises it can't. Solved ...; Created on 14 May 2013
12. Parse a HTML Table into a ListView: / Hypertext Markup Language /; ... } 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 ...; Created on 30 March 2013
13. Quickly update all content in mySQL database replacing a string: / MySQL /; ... @stringtoReplaceWith) AS newText, `content_column` as oldText FROM `MYTABLE_content` WHERE `content_column` LIKE CONCAT('%',@stringtoFind,'%') -- yields id title OldFoundInOld NewFoundInOld OldFoundInNew newText oldText 1 Article ...; Created on 03 February 2013
14. 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
15. Generate Academic Calendar using MySQL: / MySQL /; ... % (avoids MySQL Error 1065: Works within PHP scripts) SELECT calendar.WeekNumber AS AcademicWeek, CONCAT( DAYOFMONTH(calendar.StartDate), '-', ...; Created on 27 November 2012
16. Search a database for a string (MySQL, T-SQL): / Databases /; ... COUNTER INT; DECLARE table_cur CURSOR FOR SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name ...; Created on 08 October 2012
17. Copy a table with structure and data into a temporary table: / Transact-SQL /; ... (dummyIndex INT); -- Alter temporary table structure to match given table SET @TableDeclaration='ALTER TABLE #myScrambledTable ADD ' + STUFF( ( -- Code to concatenate column names and data ...; Created on 03 August 2012
18. DataJumble - Shuffling characters in a data value: / Transact-SQL /; This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed ...; Created on 01 August 2012
19. MySQL: Display Users and Duration in Matrix Timesheet: / MySQL /; ... User Query $t_value = $the_team_id_that_i_want_to_look_at; $user_list_query=" SELECT DISTINCT u.UserID, CONCAT(u.FirstName, ' ', u.LastName) AS 'Fullname' FROM Users u WHERE u.StatusType ...; Created on 27 April 2012
20. Getting the mysql where in delimited string to work: / MySQL /; What? I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room. The Problem? When I select specifying the statement "WHERE IN (c.RoomImages)", ...; Created on 02 January 2012