How to replace a string in a SQL Server Table Column

In a table which a column references string  and I need to change the same string to the new one, in every record.

Sytax

update my_table
set path = replace(Column_Name, 'oldstring', 'newstring')

you can also read:

• Add a column to an existing table in SQL Server
•  Eliminating Duplicates with DISTINCT

Example

update tblStudent
set Email = replace(Email, '@yahoo.com', '@gmail.com')

How to check if a table exists in SQL Server 2000/2005 using SQL Statement.

How to check if a table exists in SQL Server 2000/2005 using SQL Statement.

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

First Method:

IF EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tblStudent'
)
BEGIN
    -- do needful
END

Second Method:

IF OBJECT_ID(N'tblStudent',N'U') IS NOT NULL
BEGIN
    SELECT *FROM tblStudent
END


Also to check for a temporary Table, we have to query the tempdb database and use a Like operator for the table name

SELECT *FROM tempdb.INFORMATION_SCHEMA WHERE TABLE_SCHEMA =' TheSchema' AND TABLE_Name LIKE '#MyTemp%'

another method

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
BEGIN
SELECT *FROM #Temp2
END

Add a column to an existing table in SQL Server

Altering a table is common situation during development. Sometimes it create some trouble. So Here I describing how to add a column to an existing table in SQL Server

Here Table structure

CREATE TABLE tblStudent
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 StudentName VARCHAR(50) NOT NULL DEFAULT('')
)


Here,  Adding a column in email to existing tblStudent. To achieve this alter the table tblStudent
Syntax
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Example-
ALTER table to add a column with DEFAULT

ALTER TABLE tblStudent
ADD Email VARCHAR(50) NOT NULL DEFAULT('')

ALTER table to add a column with DEFAULT and  CONSTRAINT

ALTER TABLE tblStudent
ADD Contact VARCHAR(50)
CONSTRAINT cntContact DEFAULT 0 WITH VALUES

How to Encrypt password in SQL Server?

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function are PWDENCRYPT and HASHBYTES.  By the name it is clear that it will crease the password.

The syntax for PWDENCRYPT is:

  PWDENCRYPT(N’String to encrypt’)

HASHBYTES function which is available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below
HASHBYTES (ALGORITHM, ‘Text to secure’)


Here, SHA2_256, SHA2_512 available with 2012 version and above.