Using regular expression within a stored procedure

Question

What is the regular expression pattern that enables every input besides characters? So far this is what i have -

CREATE PROCEDURE Paging_Movies
@alphaChar char(1)
AS
if @alphaChar = '#'
select * from Movies where movies like '[0-9]%'
else
select * from Movies where movies like @alphaChar + '%'

Do you mean you want only numbers? Or all characters that aren't a-z? — Town
– Town, Commented Jun 15, 2011 at 23:23
this is for sql server 2008 r2, and yes all characters that aren't a-z — kurupt_89
– kurupt_89, Commented Jun 15, 2011 at 23:37

Abe Miessler · Accepted Answer · 2011-06-15 23:27:33Z

2

If you want true regular expression pattern matching you will need to roll your own CLR UDF. This link goes over how to do that:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Keep in mind that you can only do this in SQL Server 2005 or higher.

If you just want non-alpha you can do this:

'([^a-z])'

Here is the documentation for SQL Server like:

http://msdn.microsoft.com/en-us/library/ms179859.aspx

answered Jun 15, 2011 at 23:27

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Town · Accepted Answer · 2011-06-15 23:39:39Z

2

SQL Server 2008 R2 has some regular expression functions built-in.

Here's a link explaining how to extract them and use them in your own database.

answered Jun 15, 2011 at 23:39

Town

14.9k3 gold badges50 silver badges72 bronze badges

2 Comments

Abe Miessler Over a year ago

Very cool, didn't know about that. So you are still creating a CLR function right? Just from a Microsoft assembly instead of your own?

Town Over a year ago

@Abe Miessler: I haven't used it personally, but yes - it just saves you rolling your own as there are already some in there!

Collectives™ on Stack Overflow

Using regular expression within a stored procedure

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related