Technical Article

Remove Pattern Matched Characters

,

The first function recursively removes all characters matching a supplied patindex patten on a string by string basis. 

select cusno,last_name,[dbo].[xRemoveMatchedCharacters](last_name,'%[^0-9a-z]%')

 from cusmas c

select cusno,last_name,[dbo].[xRemoveMatchedCharacters](last_name,'%[0-9a-z]%')

 from cusmas c

The second version is a table based function built on the first function.

select cusno,last_name,z.*

 from cusmas c

 cross apply [dbo].[zRemoveMatchedCharacters](last_name,'%[^0-9a-z]%')z

select cusno,last_name,z.*

 from cusmas c

 cross apply [dbo].[zRemoveMatchedCharacters](last_name,'%[0-9a-z]%')z

CREATE FUNCTION [dbo].[xRemoveMatchedCharacters]
(
   @raw nvarchar(max), 
   @pattern nvarchar(max)
)
returns nvarchar(max)
as
begin
declare @Index int
set @Index = patindex(@pattern, @raw)
if @Index = 0
return @raw;

return dbo.xRemoveMatchedCharacters(replace(@raw,substring(@raw,@Index,1),''),@pattern);
end
go


CREATE FUNCTION [dbo].[zRemoveMatchedCharacters]
(
   @raw nvarchar(max), @pattern nvarchar(max)
)
   returns table
as
return
select dbo.xRemoveMatchedCharacters (@raw, @pattern)[clean]
go

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.25 (4)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.25 (4)

You rated this post out of 5. Change rating

Related content

External Article

SQL Server CLR function to concatenate values in a column

If a column is normalized, but the user really wants to see the values as a short comma separated list, how can I write a query that produces the list? Concatenating the values in a column would be pretty easy if SQL Server had a concatenate aggregate function, which it doesn't. What's more, for efficiency sake it's important to write the reporting queries without using cursors.

2009-02-09

2,240 reads

External Article

Faking Arrays in Transact SQL

It is a simple routine that we all need to use occasionally; parsing a delimited list of strings in TSQL. In a perfect relational world, it isn't necessary, but real-world data often comes in a form that requires one of the surprising variety of routines that Anith Sen describes, along with sage advice about their use.

2008-09-24

5,751 reads