SQL Tips by Namwar Rizvi

January 12, 2009

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

Here it is:

USE [AdventureWorks]
GO;

IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* Check if we get the valid base */
If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

/* variable to hold final answer */
Declare @answer as varchar(63);

/* Following variable contains all
possible alpha numeric letters for any base
*/
Declare @alldigits as varchar(36);
Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

/* Set the initial value of
final answer as empty string
*/
Set @answer='';

/* Loop until your source value is greater than 0 */
While @val>0
Begin
Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
Set @val = @val / @base;
End

</code>

<code>/* Return the final answer */
return @answer;
End

September 5, 2007

Validating Inputs and Finding Patterns with PatIndex

Filed under: string manipulation,tips,TSQL,Utility Functions — namwar @ 8:54 PM

PatIndex is one of most powerful functions of TSQL but often developers ignore it firstly because of the availability of CharIndex and secondly, the difficulty of creating patterns for PatIndex to search. Developers try to solve some typical problems faced by today’s web application by developing their own workarounds instead of using PatIndex.
Today, I will share some very useful tricks which can help you to make sure that your input string is according to the pattern you want like all numeric, all letters, starts with letter and end with number etc.

Following are the TSQL scripts which you can use to solve these type of problems. You can convert them to user defined functions as per your requirements:

—Checks that input only contains numbers

if PatIndex(‘%[^0-9]%’,’11’) > 0
Begin
Print ‘Not all numbers’
End
Else
Begin

Print ‘All numbers’
End

—Checks that input only contains letters
if PatIndex(‘%[^a-z]%’,‘aaaaa’) > 0
Begin
Print ‘Not all letters’
End
Else
Begin

Print ‘All letters’
End

–Checking for mixed input
If PatIndex(‘%[^0-9][0-9]%’,‘abc’) > 0
Begin
Print ‘Alpha numeric data’
End
Else
Begin

Print ‘Either all numbers or all letters’
End

–Checks that value must start with a letter and a number
If PatIndex(‘[^0-9][0-9]%’,‘A1anamwar11’) > 0
Begin
Print ‘Starts with a letter and a number’
End
Else
Begin

Print ‘Does not start with a letter and a number’
End

–Checks that value must End with a letter and a number
If PatIndex(‘%[^0-9][0-9]’,‘A1anamwar11a1’) > 0
Begin
Print ‘Ends with a letter and a number’
End
Else
Begin

Print ‘Does not End with a letter and a number’
End

–Checks that value must Start with a letter and Ends with a number
If PatIndex(‘[^0-9]%[0-9]’,‘namwar1’) > 0
Begin
Print ‘Starts with a letter and ends with a number’
End
Else
Begin

Print ‘Does not start with a letter and ends with a number’
End

August 25, 2007

Return Last n Orders by using APPLY operator

With many other new enhancements, SQL Server 2005
has introduced another very useful operator called
APPLY, which makes life very easy for some complex problems.
APPLY operator works as follows:
1. It applies a table valued function to each row of the table
by using the column values as parameters.
2. Resulting rows are then returned as table
which can be used as a normal table.

Following TSQL script will return Last 3 orders for every
SalesPerson in AdventureWorks database.
Just replace 3 with stored procedure parameter to get
n number of last orders from each sales person.

Use AdventureWorks
Go

–Creating function to
–return top 3 orders

Create Function
dbo.udf_GetLatestOrders(@p_SalesPersonId int)

Returns table
AS
return

Select Top 3 *
from Sales.SalesOrderHeader
Where SalesPersonId=@p_SalesPersonId
Order by ShipDate desc

GO

–Run the query to use above
–created function

Select SP.* from Sales.SalesOrderHeader S
Cross Apply
dbo.udf_GetLatestOrders(S.SalesPersonId) SP

Where
–Join function and Master table
S.SalesPersonId=SP.SalesPersonId

August 20, 2007

ENABLE/DISABLE all triggers of all tables in one statement

Filed under: Shortcuts,tips,Undocumented features,Utility Functions — namwar @ 8:15 PM

Sometimes we need to disable triggers to perform some tasks. Disabling the trigger by going to each table is very tedious. Following is avery quick way of disabling all triggers on all tables of the given database in a single statement.

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all triggers, you can use following statement

sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

June 26, 2007

T-SQL DATETIME : Last Day of the Month

Filed under: Query,tips,TSQL,UDF,Utility Functions — namwar @ 10:29 PM

Getting the last day of the month, for the given date, by TSQL is bit tricky. Following user defined function provides you a handy general purpose function to get the last day of the month.
It gets the last day of the month as follows:
For example we have a given date 17-March-2007

  1. Add a month in the given date by using DateAdd function. It will become 17-April-2007
  2. Get the Day part of the date from the above step. It is 17
  3. Subtract number of days of the second step from the date of step 1. It will become 31-March-2007
  4. Resulting date in step 3 is our answer.

Following is the TSQL code of the function

Create function
udf_GetLastDayOfTheMonth(@m_GivenDate datetime)

returns datetime
as
Begin

Return dateadd(day,-1* day(dateadd(month,1,@m_GivenDate)),dateadd(month,1,@m_GivenDate))

End

June 22, 2007

Extracting FileName through TSQL

Filed under: Query,tips,TSQL,Utility Functions — namwar @ 11:27 PM

Today I am sharing with you a utility UDF function to extract the file name from the given file path. This TSQL function also demonstrates the use of finding last occurance of a given character by using REVERSE function. Following is the TSQL code:

Create function dbo.udf_GetFileName(@m_FullFilePath varchar(255))
Returns varchar(50)

as

Begin

Return
Reverse(Left(Reverse(@m_FullFilePath),Charindex(‘\’,

Reverse(@m_FullFilePath))-1))

End

Now lets test this function:

Select dbo.udf_GetFileName(‘C:\Program Files\Adobe\Acrobat5.0\Help\ENU\ACROBAT.PDF’)

Result:
ACROBAT.PDF

Blog at WordPress.com.