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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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