User Define Function When LTrim and RTrim not Working

Ltrim and RTrim not always Working to remove white space from column because of the special character at end. It may be because user try to copy from word or excel. In that case use following function to remove space.

The UDF



CREATE FUNCTION [dbo].[udfTrim]

(
               @StringToClean as varchar(8000)
)
  RETURNS varchar(8000)




AS
  BEGIN

--Replace all non printing whitespace characers with Characer 32 whitespace

--NULL

Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));

--Horizontal Tab

Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));

--Line Feed

Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));

--Vertical Tab

Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));

--Form Feed

Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));

--Carriage Return

Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));

--Column Break

Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));

--Non-breaking space

Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));




  Set @StringToClean = LTRIM(RTRIM(@StringToClean));

Return @StringToClean




END

GO  -------------------------------------------------------------------

SSRS Expression to convert Second to Hours

Following Expression in SSRS will convert Second to Hours Minutes Second Format

HH : MM : SS

 

=Floor(sum(Fields!Second.Value)/ 3600) &":"& Format(DateAdd("s", sum(Fields!Second.Value), "00:00"),"mm:ss")

 

 

 

Following Expression in SSRS will convert Second to Days Hours Minutes Second Format

DD :HH : MM : SS

 

 

=Floor(sum(Fields!Second.Value) / 86400) &":"& Format(DateAdd("s", sum(Fields!Second.Value), "00:00:00"), "HH:mm:ss")

Exampe From Report

Creating View in SQL Server

You Can Create View in SQL by witting Query.

You Can Use SCHEMABINDING in View to hide definition of View.

By SCHEMABINDING on View you Cannot Use

-Union or Union All in View
-Distinct in View
-Sub Query and Self-Join in View
-Complex Aggregate Functions in View.

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 

Find Non Integer From SQL Column

If you want to find Non-Integer from Column in SQL Server  that does not have Data Type integer , Use following SQL Query.

Select *  From [ChildRegistration]
    Where IsNumeric(ChildID + '.0e0') = 0

If you want to find Integer from Column in SQL Server  that does not have Data Type integer , Use following SQL Query.

Select *  From [ChildRegistration]
    Where IsNumeric(ChildID + '.0e0') = 1

Add Identity Column and Populate Column in SQL Server

Add Column using SQL Statment and Populate Identity Column in SQL Server

Alter Table ChildRegistration
Add RegistrationID  Int Identity(1000000,10)

1000000 Means Value Start with 1000000 and adding in 10 increment.

Add Default Constraint GetDate() to Existing Column

Add constraint default getdate to existing column

Alter table [dbo].[Child]
Add Constraint DF_Last Default Getdate() For [LastUpdate] 

Find Non Date in Column using IsDate

Suppose We have Employee Table with Hiring Date and HiringDate is with Data Type Nvarchar.
We want to find out which one is non date in Hiring Column ?

Find Non-Date Part from Column using SQL Query IsDate


Select * from Employee
Where IsDate(JoiningDate) = 0

Find Date Part from Column using SQL Query IsDate

Select * from Employee
Where IsDate(JoiningDate) = 1



Add Calculated Column Age

Add Calculated Age Column in SQL Server through Query

Alter table Child_Record

Add Age AS (datediff(year,[BirthDate],getdate() ))   

Trim Leading (First) Charcter - TSQL Query

Trim Leading (First) Character in T-SQL Query  using Left, Right, Len Function

 
Select N, LEFT(RIGHT(N, len(N)-1),len(N)-1) as Number

From Number
 
 
 

 

 

 



 



Other Query

Select Name, stuff(Name, 1,1,'') Name from Contact

select Name, Substring(Name,2,8000) from Contact

Combine Two Column in One - TSQL Query

Select S1, S2 ,

Isnull(S1,'') + Isnull(S2,'') as State

From State

Split One Column to Three Column - SQL Query

Split One Column like Full Name to First Name, Middle Name, Last Name

 - Using CharIndex, Reverse, RTrim, Substring in TSQL

SELECT [Child Name],

LEFT([Child Name],CHARINDEX(' ',[Child Name] + ' ')-1) AS First_Name,

Rtrim(substring([Child Name],CHARINDEX(' ',[Child Name],0)+1,len([Child Name])-CHARINDEX(' ',[Child Name],0)+1-CHARINDEX(' ', REVERSE([Child Name])))) Middle_Name,

Right([Child Name],ISNULL(NULLIF(CHARINDEX(' ', REVERSE([Child Name])) - 1,-1), LEN([Child Name]))) AS Last_Name

 From Child