Sql Code Masterz

 Avoid duplicate entry while using insert into .... select  statement        Insert into Names        select name.Value as name, abr.Value as abr         from  name        INNER JOIN   abr ON name.RowID = abr.RowID        where NOT EXISTS (select 1                  from Names a                  where a.name =name.Value                  and a.abr = abr.Value)

Find and Delete duplicate records Find Duplicate Records using SQL        SELECT name, COUNT(name) AS namecount        FROM tbl_content        GROUP BY name        HAVING ( COUNT(name) > 1)  Delete Duplicate Records using SQL        delete T1 from tbl_content T1, tbl_content T2 where T1.name = T2.name and T1.id > T2.id  Delete Duplicate Records using Access       delete from       MyTable       where uniqueField not in       (select min(uniqueField) from       MyTable T2       where T2.dupField=MyTable.dupField)

Remove the string after a particular character(Making Sub string) This code may be used where we want to remove a the string after a particular character. Example :- Suppose your column have like this - ID Name 1 MNIT,Jaipur 2 GVSET,JAipur 3 MSJ,Bharatpur etc. Now you want to remove the cities name from this column. You can do this by simply using this code -         UPDATE Table_Name SET Name= dbo.substr(Name) Function should be look like this-            CREATE FUNCTION dbo.substr(@string nVARCHAR(MAX))      RETURNS nVARCHAR(MAX)          BEGIN                   DECLARE @SpaceIndex TinyInt                   SET @SpaceIndex = CHARINDEX(',', @string)           ...

Remove Spaces in columns in sql table         CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))         RETURNS VARCHAR(MAX)              BEGIN                   RETURN LTRIM(RTRIM(@string))              END        GO SELECT dbo.TRIM(' aaaaa strig ') or UPDATE Table_name SET columnname= dbo.TRIM(columnname)) or WITH trimmed AS (SELECT LTRIM(RTRIM(fld1)) as fld1, LTRIM(RTRIM(fld2)) as fld2 ....) SELECT... You can use following query for removing white spaces from all the columns UPDATE Table_name SET columnname = REPLACE ( columnname , ' ' , '' ),columnname1=     REPLACE ( columnname1 , ' ' , '' ) and so on for as many columns as you have.

CHARINDEX: Get index of the delimiting space .Net Framework has very useful String.IndexOf that returns position of first occurrence of given char or substring in string. Although there is no function named IndexOf in T-SQL you can achieve same result with CHARINDEX and PATHINDEX function. CHARINDEX function as counterpart of IndexOf() CHARINDEX function has the following syntax: CHARINDEX(expression1, expression2 [, start_location]) expression1 - sequence of characters to be found expression2 - column or expression that is searched start_location - optional parameter, start position of searching Example that search for first position of word "chart" in ProductDescription column could look like: SELECT CHARINDEX ( 'chart' , ProductDescription ) FROM Products PATINDEX function PATINDEX function works very similar to CHARINDEX. Unlike CHARINDEX, PATINDEX function has only two parameters and first parameter is pattern that can include wildcard p...