Convert SQL Text Column to Date

Convert Column to Date

--Suppose there is Text Column in SQL Server

--Suppose you want to extract only Date out of it.

--First create the following function to extract numeric data

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))RETURNS VARCHAR(256)AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )END
END
RETURN ISNULL(@strAlphaNumeric,0)ENDGO




-- Now Update Column with Function 


Update Employee

Set AwardReceive = [dbo].[udf_GetNumeric](AwardReceive)


--- You will get following result 



-- Use following 

Select AwardReceive,Left(AwardReceive,2) +'/'+ Substring(AwardReceive,3,2)+'/'+Right(AwardReceive,4)

from employee 


-- You will get following result



-- Now Update the Column and Change Data Type 


Update Employee

Set AwardReceive = Left(AwardReceive,2) +'/'+ Substring(AwardReceive,3,2)+'/'+Right(AwardReceive,4)

Phone Constraint in SQL

To Add Constraint to Any SQL Table Phone Column use following Script. 

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [Ck_Phone_Employee]
CHECK  

(

[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]' OR
[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][*][0-9]' OR
[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][*][0-9][0-9]' OR
[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][*][0-9][0-9]
[0-9]'
OR
[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][*][0-9][0-9]
[0-9][0-9]' 
OR
[Phone] like '[1-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][*][0-9][0-9
][0-9][0-9][0-9]'

)
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [Ck_Phone_Employee]

GO

This will Add Constraint with Phone Extension. Use Data Type Varchar(18) for Phone Column