SQL Text Match on Character Stream (wildcards)

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Text Match on Character Stream (wildcards)

Author

Topic

ohreallyus
Starting Member

1 Post

Posted - 2008-05-22 : 21:02:29

Hey Guys,

This may be easy...or it may be impossible!

I need to match a text field on zero or more characters. If available, the 'or more' characters need to be in a specific sequence.

The % wildcard doesn't quite cut it.

For instance, I need to match the name field with 'm', 'ma' or 'mar' (but no other character combinations).

Is this possible?

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-05-22 : 21:37:45

I'd use a regex function if I were you. Something like this

CREATE FUNCTION

dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))

RETURNS int

AS

BEGIN

declare @obj int

declare @res int

declare @match bit

set @match=0

exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT

IF (@res <> 0) BEGIN

RETURN NULL

END

exec @res=sp_OASetProperty @obj, 'Pattern', @pattern

IF (@res <> 0) BEGIN

RETURN NULL

END

exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1

IF (@res <> 0) BEGIN

RETURN NULL

END

exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring

IF (@res <> 0) BEGIN

RETURN NULL

END

exec @res=sp_OADestroy @obj

return @match

END

GO

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-05-22 : 21:39:42

btw usage:

SELECT au_lname

FROM authors

WHERE dbo.fn_regex('G.*',au_lname)<>0

au_lname

Green

Greene

Gringlesby

Ringer

Ringer

Straight

Stringer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources