Whatterz


SQL User-Defined Function: ReplaceChars

by Simon. Average Reading Time: about 2 minutes.

The SQL REPLACE function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

The syntax for this string function is the same for SQL Server, Oracle and Microsoft Access. The syntax is as follows:

REPLACE(stringToLookIn, stringToMatch, replacementsString)

The syntax is fairly straight forward, the stringToMatch parameter is the character phrase that we want to replace, the replacementsString is the character phrase that will replace any occurence of the stringToMatch parameter. If the stringToMatch phrase occurs more than once in the string, then all instances of the phrase will be replaced with the replacement string. If no matches were found then the string is returned unaltered.

If we want to match multiple items, we need to nest the REPLACE function:

REPLACE(REPLACE(stringToLookIn, stringToMatch, replacementsString), stringToMatch, replacementsString)

or set the replaced string into a new variable multiple times:

stringReturned = REPLACE(stringToLookIn, stringToMatch, replacementsString)
stringReturned = REPLACE(stringReturned, stringToMatch, replacementsString)

This is far from ideal, especially the more strings there are to be matched. This is where User-Defined Functions (UDFs) can provide the answer.

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

<|/1/>CREATE <|/1/>FUNCTION dbo.udf_ReplaceChars
(
@ReplaceList		<|/2/>VARCHAR(<| style="color: #cc66cc;">50),
@String			<|/2/>VARCHAR(<| style="color: #cc66cc;">100)
)
<|/1/>RETURNS <|/2/>VARCHAR(<| style="color: #cc66cc;">100)
<|/1/>AS
<|/1/>BEGIN
	<|/1/>DECLARE	@<|/2/>CHAR		<|/2/>CHAR(<| style="color: #cc66cc;">1),
		@Loop		<|/2/>INT
 
	<|/1/>SET @Loop  = <| style="color: #cc66cc;">0
	<|/1/>WHILE @Loop < = LEN(@ReplaceList)
	<|/1/>BEGIN
		<|/1/>SET	@Loop = @Loop + <| style="color: #cc66cc;">1
		<|/1/>SET	@<|/2/>CHAR = SUBSTRING(@ReplaceList, @Loop, <| style="color: #cc66cc;">1)
		<|/1/>SET	@String = REPLACE(@String, @<|/2/>CHAR, '')
	<|/1/>END
 
	<|/1/>RETURN		@String
 
<|/1/>END
<|/1/>GO

The function simply loops over the replace list, finding each instance of the list item in the string in which we want to replace items. The new string is then returned out of the function.

The Function In Use

A very simple use of the replace function could be as follows:

<|/1/>SELECT dbo.udf_ReplaceChars('=,/,<,>,@,~,#', columnName) <|/1/>AS newColumn, columnName
<|/1/>FROM tableName

The function is not restricted to SELECT statements. Below is an example of an UPDATE statement utilising a variable:

<|/1/>UPDATE tableName
<|/1/>SET columnName = dbo.udf_ReplaceChars('=,/,<,>,@,~,#', @variableName)
<|/1/>WHERE idName = @myId

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

This article has been tagged

, , , , , , , , , , , ,

Other articles I recommend

SQL User-Defined Function: ListToTable

A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

What is a SQL Injection Attack

Over the past few weeks, subversive elements in the international arena have decided that attacking websites is a fun thing to do! The online world has become the new battle ground between nations vying to de-stabilise rivals. This may seem all very Jack Bauer, but we are increasingly seening ‘SQL injection attacks’ eminating from countries such as Russia, China and North Korea. Of course, that doesn’t mean our countries aren’t doing the same in return, but we only see the results from foreign-based attacks.

How to Fix a SQL Injection Attack

In my previous post, What is a SQL Injection Attack, I gave a brief overview of SQL injection and Cross-Site Scripting (XSS), primarily with regard to websites. In the example given, we saw that an attack could take the form of a ‘hacked’ URL which contained either a literal SQL statement, or a hexadecimal string that could be interpreted by an insecure SQL database server.

  • Prasob

    By the following function we can replace with a particular word

    CREATE FUNCTION dbo.udf_SubstituteChars
    (
    @ReplaceList VARCHAR(50),
    @String VARCHAR(100),
    @NewString VARCHAR(50)
    )
    RETURNS VARCHAR(100)
    AS
    BEGIN
    DECLARE @CHAR CHAR(1),
    @Loop INT

    SET @Loop = 0
    WHILE @Loop < = LEN(@ReplaceList)
    BEGIN
    SET @Loop = @Loop + 1
    SET @CHAR = SUBSTRING(@ReplaceList, @Loop, 1)
    SET @String = REPLACE(@String, @CHAR, @NewString)
    END

    RETURN @String

    END

    Then execute

    select dbo.udf_SubstituteChars(‘a’,column_name,’#’)
    from table_name

    It will replace all a’s in the column_name with ‘#’s in the table