SQL Functions – STUFF and REPLACE

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

DECLARE @string1 VARCHAR(20) = 'Microsoft Server'

DECLARE @string2 VARCHAR(20) = 'SQL Server 2005'

SELECT      @string1 + ' -> ' + STUFF(@string1, 11, 0, 'SQL ')

            AS 'String 1',

            @string2 + ' -> ' + STUFF(@string2, 15, 1, '8 R2')

            AS 'String 2'
Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = 'sql 2005, sql 2008, sql 2008 r2'

SELECT @string3, REPLACE(@string3,'sql','SQL')
Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

Delete Duplicate Records – Rows and Keeping a row of Duplicate Records

By Using CTE - 

 ;WITH TBLCTE(ID,EmpName,number)
 AS
 (
 SELECT e.ID,e.EmpName,ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.ID) number FROM
 Employee e   
 )
 Delete from TBLCTE where number > 1

How to Transferring Ownership of a Schema to a User

Follow the this script and you will be able to transfer ownership of schema to another user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO UserName;