Search - Joel Lipman .Com

41. 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
42. 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
43. 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
44. CharIndex Reverse - find occurrence starting from end of string in TSQL: / Transact-SQL /; ... part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form. For argument's sake, I'm assigning this string to the variable "haystack". ...; Created on 28 January 2013
45. DataScramble - Randomizing data rows: / Transact-SQL /; ... if Given Value is date (ISDATE does not recognize CAST AS DATE) IF @OrigLen=10 AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 AND ISNUMERIC(SUBSTRING(@OrigVal, ...; Created on 07 December 2012
46. Generate Academic Calendar using MySQL: / MySQL /; ... (SQL Server 2008 R2) for CMIS Facility SELECT calendar.WeekNumber AS AcademicWeek, CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), ...; Created on 27 November 2012
47. Search a database for a string (MySQL, T-SQL): / Databases /; ... SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM @SQLTbl WHERE WHEREClause IS NULL ...; Created on 08 October 2012
48. Copy a table with structure and data into a temporary table: / Transact-SQL /; ... values SET @TableName = SUBSTRING(@SchemaTableName, CHARINDEX('.', @SchemaTableName)+1, LEN(@SchemaTableName)); SET @SqlToExecute = ''; -- Declare temporary table to use CREATE TABLE #myScrambledTable ...; Created on 03 August 2012
49. DataJumble - Shuffling characters in a data value: / Transact-SQL /; ... Given Value is date (ISDATE does not recognize CAST AS DATE) IF @OrigLen=10 AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 AND ISNUMERIC(SUBSTRING(@OrigVal, ...; Created on 01 August 2012
50. Migrate JComments from Joomla 1.5.x to Joomla 2.5.x: / Joomla /; ... parent, CASE WHEN SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2))='' THEN 0 ELSE SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2)) END AS thread_id, path, level, object_id, object_group, ...; Created on 07 June 2012