MS SQL Server

Find SQL Column Not Start with Number or Numeric

If you want to find SQL Column that dose not start with Numbers of Numeric here is the Query.

Select Address From Corporation
where Address Not LIKE '[0-9]%';

If you want to find SQL Column that start with Numbers of Numeric here is the Query.

Select Address from Corporation
where Address LIKE '[0-9]%';

SQL Query Convert CSV Column to Rows

Suppose we have Comma Separated value in Column and want to convert to Row

SQL Query to Covert ItemID into Row

SELECT Distinct AreaID, RequestID,

Split.a.value('.', 'VARCHAR(100)') AS ItemID , ModifiedDate, CreatedDate

FROM (SELECT

CAST ('<M>' + REPLACE([ItemID], ',', '</M><M>') + '</M>' AS XML)

AS ItemID , RequestID , AreaID , ModifiedDate, CreatedDate

FROM RequestItems_Graphics) AS A CROSS APPLY ItemID.nodes ('/M') AS Split(a)

Result

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