Reputation: 220
Suppose i have the following regex pattern of:
%[a-zA-Z0-9+&@#\/%=~_|$?!-:,.']%
How could I iterate through a strings characters and remove (or replace with a blank) any characters that don't match the pattern?
Upvotes: 0
Views: 11675
Reputation: 50282
This:
PatIndex('%[^a-zA-Z0-9+&@#\/%=~_|$?!-:,.']%', YourValue)
will return the character at which the pattern matches. In this case, I've added ^
to the beginning so that the pattern matches everything not in the character set.
You can then remove the character at that position, and continue, or replace all occurrences of the found character in the entire string.
FYI: to simulate the offset
parameter of CharIndex
in order to search starting at a certain character position, you can use Substring
to get a portion of the string (or even one character) and use PatIndex
on that.
Upvotes: 3
Reputation: 2647
As ErikE mentions in his answer, the PATINDEX
method is what you need, it is SLOW though.
An answer to a very similar question on SO is stackoverflow.com/a/23001916/4316831. The below code was taken from there:
DECLARE @counter int
SET @counter = 0
WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table))
BEGIN
WHILE 1 = 1
BEGIN
DECLARE @RetVal varchar(50)
SET @RetVal = (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '')
FROM Table
WHERE ID_COLUMN = @counter)
IF(@RetVal IS NOT NULL)
UPDATE Table SET
Column = @RetVal
WHERE ID_COLUMN = @counter
ELSE
break
END
SET @counter = @counter + 1
END
Upvotes: 0
Reputation: 220
Ok based on ErikE's help I made the following function
CREATE FUNCTION dbo.RemoveInvalidCharacters
(
@pattern as varchar(max),
@string as varchar(max)
)
RETURNS varchar(MAX)
AS
BEGIN
WHILE PatIndex(@pattern,@string) >0
BEGIN
SET @string=REPLACE(@string,SUBSTRING(@string,PatIndex(@pattern,@string),1),'')
END
RETURN @String
END
GO
Then all I do was call the function as:
select dbo.RemoveInvalidCharacters('%[^a-zA-Z0-9+&@#\/%=~_|$?!:,.''-]%','Test€€€String^^^')
Then the output is: TestString
Upvotes: 3