Friday, June 23, 2017

Convert GETDATE() to YYYYMMDD

How to convert getdate() to YYYYMMDD:

SELECT CONVERT(VARCHAR(35),@date,112)

Search text in stored procedure in SQL Server

How to search a text from all my database stored procedures. 

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

Wednesday, June 21, 2017

What if REPLACE function is not working on sql table column

160 is Unicode NO-BREAK SPACE so that's what you need to replace:


replace(UserName, char(160), '')
,LTRIM(RTRIM((ISNULL(REPLACE(REPLACE(REPLACE(City,CHAR(9),''),CHAR(10),''),CHAR(13),''),'')))) AS City

Monday, June 12, 2017

How to apply Count on multiple distinct columns and use Having clause

If you group by these columns then you already only get those unique records and then you can use count(*) to get how many duplicates you have.
select A,B,C, count(*) 
from table_name 
group by A,B,C 
HAVING count(*) > 1