Search - Joel Lipman .Com

1. T-SQL Conversion failed when converting the varchar to data type int: / Transact-SQL /; What? A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read ...; Created on 14 May 2013
2. URL Alias uniqueness with PHP & MySQL: / Personal Home Page /; ... TRIGGER IF EXISTS incrementUrlAlias; DELIMITER | CREATE TRIGGER incrementUrlAlias BEFORE INSERT ON my_table_name FOR EACH ROW BEGIN declare original_url varchar(255); declare url_counter int; ...; Created on 06 May 2019
3. T-SQL functions to convert Strings to Tables: / Transact-SQL /; ... start with a function that just converts a string of words delimited by spaces to a table: CREATE FUNCTION dbo.[ufn_StringToTable] ( @StringInput VARCHAR(MAX) ) RETURNS @OutputTable TABL ...; Created on 20 August 2014
4. Convert to Proper Case in T-SQL: / Transact-SQL /; ... words that you want in a specific case. CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN -- declare some variables DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); ...; Created on 04 June 2014
5. T-SQL: Parse an XML value: / Transact-SQL /; ... AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender FROM [dbo].[XML_EVENTS] -- Returns column GENDER for every record/row (eg. "Male") Or -- Using .value() SELECT CAST(Event_XML ...; Created on 27 March 2014
6. SSRS: Performance Improvements: SELECT TOP: / SQL Server Reporting Services /; ... = @GivenRef OR @GivenRef ='') AND (Forenames LIKE @GivenForenames + '%' OR @GivenForenames = '') AND (Surname LIKE @GivenSurname OR @GivenSurname = '') AND (CONVERT(VARCHAR(10),DOB, 103) = @GivenDOB ...; Created on 07 March 2014
7. Compare two databases using T-SQL: / Transact-SQL /; ... AS VARCHAR) + ' (new)' WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NULL THEN CAST(t1.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ...; Created on 24 September 2013
8. 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
9. Reorder Columns in a Table: / Databases /; ... MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment' AFTER other_column; Alternatively Export the file as a SQL file (data & structure), ...; Created on 22 July 2013
10. T-SQL concatenate an incremental row number: / Transact-SQL /; ... (padded with zeros) How? So how do we do it? In the above example, Joel Lipman has two qualification records. So let's start with a simple query: SELECT RIGHT('000' + CAST(e.ID AS VARCHAR), ...; Created on 24 May 2013
11. Cheat Sheet for mySQL vs t-SQL: / Databases /; ... (dd/mm/yyyy) DATE_FORMAT(now(), '%d/%m/%Y') CONVERT(varchar, getdate(), 103) Time to Seconds TIME_TO_SEC(now()) (DATEPART(hour, getdate()) * 3600) + (DATEPART(minute, getdate()) * 60) + (DATEPART(second, ...; Created on 31 January 2013
12. CharIndex Reverse - find occurrence starting from end of string in TSQL: / Transact-SQL /; ... How? Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle): DECLARE @Haystack VARCHAR(31); SET @Haystack = 'String1.String2.String3.String4'; -- ...; Created on 28 January 2013
13. DataScramble - Randomizing data rows: / Transact-SQL /; ... already exists IF OBJECT_ID ('ufn_DataScramble', 'FN') IS NOT NULL DROP FUNCTION ufn_DataScramble ; GO -- Create user defined function CREATE FUNCTION ufn_DataScramble ( @OrigVal varchar(max) ) RETURNS ...; Created on 07 December 2012
14. DataTumble - Randomize Data Rows: / Transact-SQL /; ... VARCHAR(MAX), @ColumnName VARCHAR(MAX), @WhereClause VARCHAR(MAX) = NULL ) AS /***************************************************************************************************************** ** ...; Created on 07 December 2012
15. 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
16. Search a database for a value and count matching rows: / Databases /; ... 'P') IS NOT NULL DROP PROCEDURE [usp_CountRecordsPerTablePerColumn]; GO CREATE PROCEDURE [usp_CountRecordsPerTablePerColumn] ( @p_Value int, @p_Column varchar(max) ) AS /*********************************************************************************** ** ...; Created on 05 November 2012
17. Stored Procedure to List Distinct Values and Counts: / Transact-SQL /; ... @p_SearchTable VARCHAR(max), @p_SearchColumns VARCHAR(max), @p_UseCollation VARCHAR(max) = 'Latin1_General_CS_AS' ) AS /***************************************************************************************************************** ** ...; Created on 26 October 2012
18. Convert a delimited string to table: / Databases /; ... T-SQL: DECLARE @myStringToParse VARCHAR(max), @myXML XML; SET @myStringToParse = 'Title,Forenames,Surname'; SELECT @myXml = CONVERT(xml,'' + REPLACE(@myStringToParse,',','') + ''); SELECT [Value] ...; Created on 26 October 2012
19. Search a database with SOUNDEX: / Databases /; ... PROCEDURE [usp_GenerateSearchbySoundexQuery] ( @p_SearchString VARCHAR(max), @p_TableSchema VARCHAR(max) ) AS /***************************************************************************************************************** ** ...; Created on 11 October 2012
20. Search a database for a string (MySQL, T-SQL): / Databases /; ... You can put a distinct on it (not forgetting the collate). This example is expanded in a further article called "Search a database with soundex": /* USING A CURSOR */ DECLARE @SqlToExecute nvarchar(max); DECLARE ...; Created on 08 October 2012