Find AlphaNumeric Characters Only from a String

     In SQL Server 2005, 2008 or 2012 if to find Numeric Characters only or AlphaNumeric Characters only from a given String is required then we can go for a user defined function to get the task done.

    To find only Numeric Characters, write the below user defined function:

CREATE FUNCTION dbo.udf_NumericChars

(

@String     VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN




  DECLARE @RemovingCharIndex INT

  SET @RemovingCharIndex = PATINDEX('%[^0-9]%',@String)




  WHILE @RemovingCharIndex > 0

  BEGIN

    SET @String =
STUFF(@String,@RemovingCharIndex,1,'')

    SET
@RemovingCharIndex = PATINDEX('%[^0-9]%',@String)

  END




  RETURN @String




END







------- For an
example ---------

SELECT DBO.udf_NumericChars('ADF[LJ64,;5AS90D')




-- Output

64590

    But for a function to get the AlphaNumeric characters you need a small change in PATINDEX as below :

CREATE FUNCTION dbo.udf_AlphaNumericChars 

(

@String     VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN




  DECLARE @RemovingCharIndex INT

  SET @RemovingCharIndex = PATINDEX('%[^0-9A-Za-z]%',@String)




  WHILE @RemovingCharIndex > 0

  BEGIN

    SET @String =
STUFF(@String,@RemovingCharIndex,1,'')

    @RemovingCharIndex =
PATINDEX('%[^0-9A-Za-z]%',@String)

  END




  RETURN @String




END







------- For an
example ---------

SELECT DBO.udf_AlphaNumericChars('ADF[LJ64,;5AS90D')




-- Output

ADFLJ645AS90D

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Number of days between two dates that don't have years

 Getting number of days between two dates is simple in SQL, write as :

SELECT DATEDIFF(DD,FromDate,ToDate)

And you will get the days count between these dates.

But on msdn forum I found an user had issue, which I am adding here is :

A cattle rancher buys cattle, holds them for 10 - 12 months and then sells them.  During the summer, he grazes them which significantly lowers his production cost (grass is much cheaper than feeding corn).  The dates being used for summer grazing are 5/15 to 9/20, regardless of the year.  The buy and sell dates can vary throughout a calendar year (see sample data).

Now, some sample data:

DECLARE
@LowExpensePeriod TABLE (StartDate NVARCHAR(6), EndDate NVARCHAR(6));

INSERT INTO @LowExpensePeriod (StartDate, EndDate) VALUES('5/15', '9/20');

SELECT * FROM
@LowExpensePeriod;



DECLARE @Trx TABLE (Buy date, Sell date);

INSERT INTO @Trx (Buy, Sell) VALUES



--dates fall in
same year



('6/1/2012', '9/1/2012'),   --All days between
low expense period

('3/1/2012', '10/30/2012'), --Each side of low expense period

('3/1/2012', '8/31/2012'),  --Left side of low
expense period

('5/25/2012', '10/30/2012'),--Right side of low expense period

--dates span two
years

('8/10/2012', '6/1/2013');  --From each year in
low expense period



SELECT *, DATEDIFF(d, Buy, Sell) AS HoldingPeriod, '?' AS
DaysInLowExpensePeriod FROM @Trx;

The question - how is it possible to compute the number of days in the low expense period accurately for each of the sample Buy+Sell date scenarios? [The goal is to replace the '?' in the column called DaysInLowExpensePeriod with the number of days that fall in the low expense period for each set of Buy+Sell dates].

Below is the Suggested answer :

SELECT
*

 ,DATEDIFF(DD, BuyDate, SellDate) AS HoldingPeriod

 ,CASE WHEN DATEDIFF(DD,BuyDate,'9/20/'+STR(YEAR(BuyDate)))>0 THEN

    CASE WHEN DATEDIFF(DD,BuyDate,'5/15/'+STR(YEAR(BuyDate)))>0 THEN 128

    ELSE DATEDIFF(DD,BuyDate,'9/20/'+ STR(YEAR(BuyDate))) END

  ELSE
0 END

 +CASE WHEN DATEDIFF(DD,'5/15/'+STR(YEAR(SellDate)),SellDate)>0 THEN

   CASE WHEN DATEDIFF(DD,'9/20/'+STR(YEAR(SellDate)),SellDate)>0 THEN 128

   ELSE DATEDIFF(DD,'5/15/'+STR(YEAR(SellDate)),SellDate) END

  ELSE 0 END

 +CASE WHEN DATEDIFF(YY,BuyDate,SellDate)>0 THEN (DATEDIFF(YY,BuyDate,SellDate)-1)*128 

  ELSE 0 END AS DaysInLowExpensePeriod 

FROM
@Trx;

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

      If you are using recursive query in CTE for Traversing a hierarchy; then there is a chance to get the mentioned error. This comes when your data has hierarchy depth greater than 100. Because by default SQL allows MAXRECURSION upto the 100th level. While the Max recusion length is 32767, so you can change this option and set your desired depth upto 32767.

Let's understand it by taking an example below :

----- Create a table -------





CREATE TABLE TestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(50)

,ParentID   VARCHAR(10)

)



----- Insert the records into TestTable -------





INSERT INTO TestTable VALUES
(10001,'User1',NULL)



DECLARE @LOOPCOUNT
INT, @UserID INT, @ParentID INT



SET @LOOPCOUNT
= 1

SET @UserID = 10001

SET @ParentID = NULL



WHILE (@LOOPCOUNT < 200)

   BEGIN

       INSERT INTO TestTable

       SELECT  @UserID+@LOOPCOUNT
AS UserID 

              ,'User' + CAST(@LOOPCOUNT AS VARCHAR) AS UserName

              ,@UserID+@LOOPCOUNT-1 AS ParentID

       SET @LOOPCOUNT = @LOOPCOUNT + 1

   END

Now see the records inserted in the table TestTable :

SELECT * FROM TestTable



 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

  Now if you wish to see the users mapped and down the hierarchy of UserID '10001' and executed the below query :

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

You will get the error message:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

So now, to avoid the error, give OPTION (MAXRECURSION 200) or any other range or OPTION (MAXRECURSION 0) to get max range...

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

OPTION (MAXRECURSION
0)

And you are done ...


Please Note that casting is done to avoid another error:

Msg 240, Level 16, State 1, Line 1. Types don't match between the anchor and the recursive part in column "a" of recursive query "CTE".   Which is caused by datatype mismatch...

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Types don't match between the anchor and the recursive part in column of recursive query "CTE".

Msg 240, Level 16, State 1, Line 4
Types don't match between the anchor and the recursive part in column "a" of recursive query "CTE".

     "Types don't match between the anchor and the recursive part" says that when you are using Common Table Expression (CTE), a perticular column "a"(as here mentioned in the error message) has a different DataType in Anchor Part with that of in Recursive Part. Anchor part is the part above the "UNION ALL" and Recursive Part is the part below it.

     Please note that the column DataType in these parts is not automatically converted to a higher precedence DataType to make a match. See the example below :

----- Create a table -------





CREATE TABLE TestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(50)

,ParentID   VARCHAR(10)

)



----- Insert the records into TestTable -------





INSERT INTO TestTable VALUES
(10001,'User1',NULL)

DECLARE @LOOPCOUNT
INT, @UserID INT, @ParentID INT

SET @LOOPCOUNT
= 1

SET @UserID = 10001

SET @ParentID = NULL

WHILE (@LOOPCOUNT < 50)

   BEGIN

       INSERT INTO TestTable

       SELECT  @UserID+@LOOPCOUNT
AS UserID 

              ,'User' + CAST(@LOOPCOUNT AS VARCHAR) AS UserName

              ,@UserID+@LOOPCOUNT-1 AS ParentID

       SET @LOOPCOUNT = @LOOPCOUNT + 1

   END

Now see the records inserted in the table TestTable :

SELECT * FROM TestTable



 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

   
 Now if you wish to see the users mapped and down the hierarchy of UserID '10001' and executed the below query :

;WITH CTE

AS

(

SELECT  '10001' UserID

       ,'User1' UserName

       ,NULL    ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

  
You will get the error message:

Msg 240, Level 16, State 1, Line 1

Types don't match between the anchor and the recursive part in column "UserID" of recursive query "CTE".

 This is because, in Anchor Part UserID has value '10001' whose length is of 5 characters so its datatype will be char(5), but in TestTable it is decalred as VARCHAR(10). The same is applied to UserName and ParentID as well.

To avoid this error, make a datatype casting. See below :

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

  
This time you will not get the error, and will have the output as below:

 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

 Note: If hierarchy has quite a high and recursion goes beyond 100, then you will face another error as:

Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Remote table-valued function calls are not allowed

 Remote table-valued function calls are not allowed. Msg 4122, Level 16, State 1, Line 1.

     I was doing some task and found the said error. I googled and found that this error may come in different cases, for two cases I have seen are .. 

 1) First case comes when you are calling a table from remote server, but at the same time you are giving a (NOLOCK) Hint. For an example See below :

SELECT * FROM [50.15.250.70].BSE.DBO.EmployeeMaster (NOLOCK)
 ----- And the
output is : ------  




Msg  4122,
Level 16, State 1, Line 1

Remote table-valued function calls are not allowed.

 THIS CAN BE AVOIDED BY ADDING 'WITH' BEFORE (NOLOCK) HINT, SEE BELOW :

SELECT * FROM [50.15.250.70].BSE.DBO.EmployeeMaster WITH (NOLOCK)

  ----- And the
output is : ------   




EmpID EmpName     Address     ContactNo

U001  Joseph      UK          2222222222

U002  Anil        India       5555555555

U003  Bruce       China       3333333333

U004  Simmy       Canada      4444444444

……..  ……          …….         …….

2) Second case comes when you are calling a table valued function from the Remote Server using liked server, see below :

SELECT * FROM
[50.15.250.70].BSE.DBO.tvf_TestFunction()
----- And the
output is : ------  






Msg  4122,
Level 16, State 1, Line 1

Remote table-valued function calls are not allowed.

    This is because in MSSQL Server, calling a remote table valued function is not supported..





Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Find all Tables Containing a Column Name

    If you like to get all the tables in a database which contains a column name you specified, then write the below queries and get it easily:

SELECT  C.name AS ColName, T.name AS TableName

FROM     sys.columns C

        JOIN sys.tables T 

        ON C.object_id = T.object_id

WHERE   C.name LIKE '%MyColName%'




--- Or...---




SELECT  COLUMN_NAME,
TABLE_NAME 

FROM    INFORMATION_SCHEMA.COLUMNS 

WHERE   COLUMN_NAME LIKE '%MyColName%'

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Find all Views and SP which depends on a Table

     If you wish to Select all the Views and SP's which depends on a table then we can find it from the sys.sysdepends table. It contains all the depedencies of all the tables.

    Let's say you have a table 'MstEmployee' and you wish to find all the Views and SP's in which this table is referred, then write below query :

SELECT  DISTINCT OBJECT_NAME(DP.ID) ObjName

FROM    sys.sysdepends
DP

        INNER JOIN  sys.sysobjects OB

        ON DP.DepID = OB.ID

WHERE   OB.NAME = 'MstEmployee'







---- Output
--------



ObjName



USP_NewEmployees

USP_Employee_Insert

USP_GetEmployeeDetails

USP_CheckEmployeeExistance

VW_CorporateEmployees

Reference: Govind Badkur(http://govindbadkur.blogspot.com)