Here’s a handy query for finding duplicates in a table using the GROUP BY and HAVING statement. Suppose you want to find all email addresses in a table that exist more than once: SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) You could also use this technique […]
SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112) This is a way to query and get the date in the following format: YYYYMMDD I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar.
View Standard Date Formats
Error: Back up the transaction log for the database to free up some log space. To recover from a full transaction log If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use: DUMP TRANSACTION dbName WITH […]
Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
RIGHT Returns the part of a character string starting a specified number of integer_expression characters from the right. Example CREATE TABLE #CustomerNames ( CustomerId int, CustomerName varchar(25) ) INSERT INTO #CustomerNames SELECT 1, ‘Bob Johnson’ UNION ALL SELECT 2, ‘Al Riley’ UNION ALL SELECT 3, ‘Mike Henry’ SELECT RIGHT(CustomerName, 5) FROM #CustomerNames Results hnson Riley […]
SELECT * FROM VTM_DATA_DETAILS WHERE (PRO_NUMBER = ANY (SELECT pro_no FROM vtm_data_combination WHERE (total_weight = 0) AND (total_charges = 0) AND (total_pieces = 0))) This query is essentially, imagining PRO_NUMBER is a 3 digit number: SELECT * FROM VTM_DATA_DETAILS WHERE (PRO_NUMBER = 097) OR (PRO_NUMBER = 647) OR (PRO_NUMBER = 324)…..etc. Until all PRO_NUMBER are […]
Another way to go about changing the data type varchar to decimal is to change the varchar to money then convert the money data type to decimal. It may not be the best way to do it but it has work for me.
The function returns 1 for numeric values, and 0 for non-numeric values. CREATE TABLE #CustomerNames ( CustomerId int, CustomerName varchar(25) ) INSERT INTO #CustomerNames SELECT 1, ‘Bob Johnson’ UNION ALL SELECT 2, ‘Al Riley’ UNION ALL SELECT 3, ‘Mike Henry’ SELECT ISNUMERIC(CustomerID), ISNUMERIC(CustomerName) FROM #CustomerNames Results 1 0 1 0 1 0
Here’s a way to convert a VARCHAR to DECIMAL, wrap the CONVERT in a CASE and IsNumeric function to make sure that it doesn’t cause errors when the varchar data is non-numeric DECLARE @test VARCHAR(25) –Numeric SET @test = ‘2.55’ SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE ‘0’ END) –Non-Numeric SET @test […]