SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 remove illigal characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Yak Posting Veteran

52 Posts

Posted - 11/05/2012 :  15:24:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  19:24:39  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000