Home > SQL Server > Filler and Counter – SQL SERVER User Defined Function

Filler and Counter – SQL SERVER User Defined Function


SQL Server has  plenty of built-in functions that help developers to use the functionality that simplifies and reduces the development time. But, sometimes we may not get some functionality thru built-in functions. So, we have to develop user defined stored procedure or function to accomplish our task.

In general, functions and procedures are reusable code in the application that we can share with other developers during development process. It is developed once and called many times as we need.

Today, I am going to share two user defined functions that may help us in application development. It is an open source code. So fell free to use.

User Defined Functions

udf_CharFiller(@input_text, @filler_character, @actual_size_of_input_text)

– This function returns the given string value with right justify and fill leading filler character.

udf_CharCounter(@input_text, @char_to_count)

– This function returns the count the given character in the input string value

Function Definition

— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Ayyappan Thangaraj
— Create date: 3/22/2014
— Description:    This function is used to lead fill a given character in a string
— =============================================
CREATE FUNCTION udf_CharFiller
(
@txt varchar(max),
@fillerchar varchar(1),
@txtsize int = 0
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @rttext varchar(max)

set @rttext = REPLICATE(@fillerchar, @txtsize – len(@txt)) + @txt

RETURN @rttext

END
GO

— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Ayyappan Thangaraj
— Create date: 3/22/2014
— Description:    This function reutrns the count of a given character in a string value.
— =============================================
CREATE FUNCTION udf_CharCounter
(
@txt varchar(max),
@searchchar varchar(1)
)
RETURNS int
AS
BEGIN
declare @charcount as int

set @charcount = len(@txt) – len(replace(@txt, @searchchar, ”))
RETURN @charcount

END
GO

Implementation

1. In this example, I am going to show how to use ufd_CharFiller function.

I have created a table with sample values as given below. It is a string column containing numbers. So, I am going to use ufd_CharFiller function to fill the string with leading *.

num is a character column of size 8. These are the sample values
23
1
123
5332

SQL Query

SELECT
dbo.udf_CharFiller(num, ‘*’, 8) as Filler,num
FROM [dbo].[tblfiller]

Output

Filler            num
******23    23
*******1    1
*****123    123
****5332    5332

2. In this example, I am going to show how to count a character in a string value using ufd_CharCount function

I have already created a table with some sample value as shown below

txt is a varchar column name of size 10. These are the sample values
1*2*3
***3
***2
1***2
29***
0**1*1*

SQL Query

select dbo.udf_CharCounter (txt, ‘*’) as charcount, txt from tbltext

Output

charcount    txt
2                      1*2*3
3                      ***3
3                      ***2
3                     1***2
3                     29***
4                    0**1*1*

I think, these functions may be helpful to someone. So, please share this blog post with your friends or in your website.

I you need a special user defined function for your project feel free to post your requirement in the comment section.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: