Finding Duplicates with SQL

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 to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

SQL: YYYYMMDD

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.

Back up the transaction log for the database to free up some log space.

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 NO_LOG

And for version 7 or later:

BACKUP LOG  WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.

SELECT RIGHT

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
Henry

SELECT WHERE ANY

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 selected from vtm_data_combination.

IsNumeric function

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

Convert from varchar to decimal

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 = 'Bob'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

Results
—————————————
2.55

—————————————
0.00