remove illigal characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

remove illigal characters

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2012-11-05 : 15:24:04

Hello everybody,

Can somebody help me with a query which removes all the illigal characters in a string.
I just want a-z and A-Z characters. I want to use the query in a trigger to alter the sting inserted by a user.
For example:
A user inserts: "L-P-A123&abc"
The query should remove the illigal characters so the result will be: "LPAabc"

Can somebody help me?

Thank You

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 19:24:39

If you are able to do so, restrict your inserts into a stored proc where you can replace the incoming string before you insert. But if you do want to use a trigger, you can do as shown below.

You need a numbers table for the code below. If you don't have one, create one like this - it should have as many numbers as the maximum number of characters you expect in the input string.

-- create a numbers table.
CREATE TABLE N(n INT NOT NULL PRIMARY KEY CLUSTERED);

;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 100)
INSERT INTO N SELECT n FROM N OPTION (MAXRECURSION 0);

Then, create the trigger like shown below. I am creating a test table for creating the trigger first.

-- create a test table
CREATE TABLE dbo.TblA (x VARCHAR(32));

Now your trigger would be like shown below:

CREATE TRIGGER dbo.TblATrigger ON dbo.TblA
INSTEAD OF INSERT
AS

DECLARE @pattern VARCHAR(32)= '[^a-zA-Z]';

INSERT INTO dbo.TblA
SELECT c FROM 
(

	SELECT
		x AS [text()]
	FROM
	(
		SELECT
			SUBSTRING(INSERTED.x,n,1) AS x
		FROM
			N
			CROSS JOIN INSERTED 
		WHERE
			n <= LEN(INSERTED.x)
			AND SUBSTRING(INSERTED.x,n,1) NOT LIKE @pattern
	)s FOR XML PATH('')
)T(c)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources