Migrate Joomla! 1.5.x to 2.5.x+https://www.joellipman.com/articles/cms/joomla/migrating-from-joomla-15-to-16.html

30 January 2011 the string ":|joes|" UPDATE `my_new_database`.my_prefix_assets b, `my_new_database`.my_prefix_categories a SET b.`title`=TRIM(SUBSTR(b.`title`, 1, LOCATE(' :|joes|', b.`title`))), b.`name`=CONCAT('com_banners.category.', a.`id`) WHERE b.`title` LIKE '%...

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

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

14 March 2013 + "/" + SUBSTRING(This_Date,9,2) + "/" + SUBSTRING(This_Date,1,4)) -- Assuming [This_Date]="21032012" (DDMMYYYY) (TRIM(This_Date)=="")? (DT_DBTIMESTAMP)"1901-01-01 00:00:00" : (DT_DBTIMESTAMP)(SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) +...

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

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

07 May 2013 x1 (eg. if word is twice then 2000pts) // + 60 pts if in introtext // + 40 pts if in fulltext $sql_keyword = strtolower(trim($db->Quote($db->escape($text, true), false))); $query->select(' (1000*((LENGTH(TRIM(a.title)) -...

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

Trim in T-SQL and SSIShttps://www.joellipman.com/articles/microsoft/ssis/trim-in-t-sql-and-ssis.html

14 May 2013 methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines). How? First in Transact-SQL: SELECT RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') ) -- CHAR(9) = Tab -- CHAR(10) = Line Feed...

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

Trim values along with the tabhttps://www.joellipman.com/articles/database/mysql/trim-values-along-with-the-tab.html

13 June 2016 A quick article on how to trim in MySQL along with getting rid of any leading or trailing tab characters. Why? I use MS Excel for organizing data and then converting to MySQL commands. Unfortunately, the MS Excel software adds tab characters to delimit...

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

No rows returned in Oracle causes SP to failhttps://www.joellipman.com/articles/database/pl-sql/no-rows-returned-in-oracle-causes-sp-to-fail.html

24 January 2012 NVL(student_accounts.studentID, 0) INTO v_studentreference FROM student_accounts WHERE student_accounts.studentUsername = trim(:p_myinparameter); SELECT NVL(student_details.studentFirstname, 'John') "FORENAMES", NVL(student_details.studentSurname,...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

Converting SQL date in PHP to European date format and vice-versahttps://www.joellipman.com/articles/web-development/php/converting-sql-date-in-php-to-european-date-format-and-vice-versa.html

17 September 2009 my_date_field FROM my_table WHERE my_id='$this_id'"), 0, "my_date_field"); $this_date_array=explode("-", trim(substr($this_date, 0, strpos($this_date, " ")))); $this_time=trim(substr($this_date, strpos($this_date, " ")+1));...

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

Data Randomization Function in Oracle PL/SQLhttps://www.joellipman.com/articles/database/pl-sql/data-randomization-function-in-oracle-plsql.html

22 August 2012 v_returnedval := SUBSTR(v_returnedval,1,LENGTH(p_original_value)); ELSIF p_original_datatype='VARCHAR' THEN IF LENGTH(TRIM(TRANSLATE(p_original_value, 'abcdefghijklmnopqrstuvwxyz ', ' '))) IS NULL THEN -- Determined submitted value as 'ALPHALOWER';...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCasehttps://www.joellipman.com/articles/microsoft/ssis/ssis-script-convert-uppercase-to-mixed-case-using-titlecase.html

04 October 2013 { ValueToConvert = ValueToConvert.Replace(exceptionArray_this[i], exceptionArray_that[i]); } } // trim to remove the trailing space ValueToConvert = ValueToConvert.Trim(); // Use a method to set the value of each String type property // Make sure the...

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

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

13 February 2023 the Message Handler code ChatGTP 3.5 Turbo // // initialize m_Response = Map(); l_Messages = List(); v_Message = message.trim(); // // if the message contains a question mark, send it to OpenAI if(v_Message.contains("?")) { v_Question = v_Message; // //...

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

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

23 April 2013 the idea that the Carriage Return (CR) or Line Feeds (LF) are being inserted, simply extract the entire row as a column and trim it. I separate out the first column as my index/key field so I can check if this is a valid data row (the blank ones may...

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

URL Alias uniqueness with PHP & MySQLhttps://www.joellipman.com/articles/web-development/php/url-alias-uniqueness-with-php-mysql.html

06 May 2019 and underscores. Note that the PHP doesn't need a database connection here: function getUrlAlias($p_Name){ // lowercase and trim preceding/trailing spaces $v_Output = strtolower(trim($p_Name)); // replace all non alphanumeric characters (except...

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

AutoHotkey: App GUI Listview to Rename Fileshttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-app-gui-listview-to-rename-files.html

08 July 2020 ; loop until there are no more double spaces while InStr( v_NewName, " ") v_NewName := StrReplace( v_NewName, " ", " ") ; trim any leading/trailing spaces and put back the extension v_NewName := Trim( v_NewName ) v_NewNameWithExt := v_NewName "."...

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

SSRS AlphaNumeric Parameter Validationhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-alphanumeric-validation.html

07 November 2011 level checks for alphanumeric values: -- ORACLE -- 0 if is alphanumeric, 1 or more if not alpha numeric. SELECT (LENGTH(TRIM(TRANSLATE(:ParameterToCheck, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ' ')))) AS NotAlphaNumeric FROM...

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

Zoho Creator: Download File from ZohoCRM field type "File Upload" (not attachments)https://www.joellipman.com/articles/crm/zoho/zoho-creator-download-file-from-zohocrm-field-type-file-upload-not-attachments.html

02 February 2021 File_Ref=v_ThisFileCrmID Expiry_Date_Time=v_ExpiryTime File_Download=r_DownloadFile ]; v_NewCreatorID = if(r_HoldInCreator.trim()!="", r_HoldInCreator.toLong(), 0); if(v_NewCreatorID != 0) { // re-get this record (to get the internal file name)...

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

Zoho Deluge: Proper Case for Nameshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-proper-case-for-names.html

13 July 2023 in l_Exceptions { v_LastName = v_LastName.replaceAllIgnoreCase(v_Exception, v_Exception, false); } v_LastName = v_LastName.trim(); // // join all together v_FullName = trim(v_FirstName + " " + v_LastName); info v_FullName; // // yields: Billie-Jo...

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

MS Excel: Convert a text to a numberhttps://www.joellipman.com/articles/microsoft/excel/ms-excel-convert-a-text-to-a-number.html

15 November 2013 is as helpful as the Microsoft paperclip, in other words, not worth the time or effort to try (didn't work anyway). The TRIM() function will not work for you either, nor with the VALUE() function.... The culprit was a suspicious space which had been...

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

Using a HTML form and PHP to upload a filehttps://www.joellipman.com/articles/web-development/php/using-a-html-form-and-php-to-upload-a-file.html

17 September 2009 chmod("/var/www/html/images/bu_roomassets/" . $_FILES["uploaded_file"]["name"], 0755); } } } else { if (trim($_FILES["uploaded_file"]["type"])=="") { echo "No file submitted for upload."; } else { echo "Invalid file type!!! You tried to upload a file...

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

Creating a Top 10 chart with less codehttps://www.joellipman.com/articles/web-development/php/creating-a-top-10-chart-with-less-code.html

18 February 2010 DISTINCT column1 FROM table1"); while($row=mysql_fetch_assoc($search_table_results)) { $this_value=trim($row['column1']); $this_value_num=mysql_num_rows(mysql_query("SELECT column1 FROM table1 WHERE column1='$this_value'")); if (($this_value!="") &&...

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

PHP & MySQL Search Enginehttps://www.joellipman.com/articles/web-development/php/php-a-mysql-search-engine.html

09 March 2011 marks and apostrophes may be an issue; Full PHP: $sanitized_search=(isset($_GET['search'])&&($sanitized_search==""))?trim(strip_tags(addslashes($_GET['search']))):""; # 2 functions to split up search terms and accepting double-quotes as phrase makers....

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