Pattern matching help... 5

[alexjones]

I'm looking at a varchar(15) field containing 1 - 15 numeric characters. I am tasked with selecting only those records for which this field is NOT all 7's or all 8's or all 9's.

If there's a (set of) regular expression(s) out there (or any other "reasonable" technique) to accomplish this, I can't see it.

Any help with this problem would be appreciated.

 

[bborissov]

Not the most elegant solution:

SELECT *
       FROM MyTable;
WHERE NOT (LEN(REPLACE(YourField,'7','')) = 0 OR
           LEN(REPLACE(YourField,'8','')) = 0 OR
           LEN(REPLACE(YourField,'9','')) = 0)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

What version of SQL are you using?

and what would you want to do with a value like '789'?

Anyway, from my understanding I think this will work:

[COLOR=blue]declare[/color] @i [COLOR=blue]table[/color] (n [COLOR=blue]varchar[/color](15))

[COLOR=blue]insert[/color] @i
[COLOR=blue]select[/color] 1234
union all [COLOR=blue]select[/color] 777
union all [COLOR=blue]select[/color] 789
union all [COLOR=blue]select[/color] 7889
union all [COLOR=blue]select[/color] 67777
union all [COLOR=blue]select[/color] 12
union all [COLOR=blue]select[/color] 8888
union all [COLOR=blue]select[/color] 9999


[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @i
[COLOR=blue]where[/color] [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%[^7]%'[/color], n) > 0
and [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%[^8]%'[/color], n) > 0
and [COLOR=#FF00FF]patindex[/color]([COLOR=red]'%[^9]%'[/color], n) > 0

Hiope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Here we go, in all its glory:

declare @table1 table (col1 varchar(15))
insert into @table1 values ('8999999')
insert into @table1 values ('9999')
insert into @table1 values ('7')
insert into @table1 values ('899435699')

select col1 from @table1 where left(col1, len(col1)) not in (replicate('9', len(col1)), replicate('8', len(col1)), replicate('7', len(col1)))

<.

 

[ESquared]

SELECT *
FROM MyTable
WHERE MyField LIKE '%[^789]%'

unless you mean

SELECT *
FROM MyTable
WHERE MyField NOT IN ('777777777777777', '888888888888888', '999999999999999')

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

[COLOR=blue]DECLARE[/color] @MyTable [COLOR=blue]TABLE[/color] (YourField [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'1111111111111111111'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'1111111asdasdasd'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'1111111112312311111'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'7777'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'7777777777777777777'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'8888888'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'8888888888888'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'9'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'999'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @MyTable [COLOR=blue]VALUES[/color] ([COLOR=red]'9998877'[/color])

[COLOR=blue]SELECT[/color] *
       [COLOR=blue]FROM[/color] @MyTable
[COLOR=blue]WHERE[/color] NOT (LEN([COLOR=#FF00FF]REPLACE[/color](YourField,[COLOR=red]'7'[/color],[COLOR=red]''[/color])) = 0 OR
           LEN([COLOR=#FF00FF]REPLACE[/color](YourField,[COLOR=red]'8'[/color],[COLOR=red]''[/color])) = 0 OR
           LEN([COLOR=#FF00FF]REPLACE[/color](YourField,[COLOR=red]'9'[/color],[COLOR=red]''[/color])) = 0)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

How about we remove the

left(col1, len(col1))

and just make it

col1

<.

 

[ESquared]

Um.

If borislav's code gives the answer you're looking for, you're better off just doing what I suggested. It's not only more efficient but it's immediately apparent what's going on:

SELECT *
FROM MyTable
WHERE MyField NOT IN ('777777777777777', '888888888888888', '999999999999999')

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[alexjones]

Thank you all for your quick responses. These worked like gangbusters. Stars all around!!!

 

[ESquared]

No, I put my foot in my mouth. I didn't think about varying length. Bah.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

SandieJ, what exactly were you looking for?

Should 789789 be IN the results or OUT of them?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[alexjones]

ESquared -

Sorry I wasn't more clear. I have to ignore fields that are all 7's or all 8's or all 9's, independent of their length. So, for example, '7', '77', '7777', ..., '777777777777777', '8', '888', etc. would all be disqualified from the result set.

Alex -

This particular problem applies to a SS2K database, but I'll have similar situations in the SS2K5 databases to which we'll be migrating. Were you thinking of something particularly whizbang in SS2K5?

 

[ESquared]

So all my answers were wrong! Sorry about that.

So finally my contribution which should meet the criteria now that I understand them:

SELECT *
FROM MyTable
WHERE
   MyField LIKE '%[^7-9]%'
   OR MyField LIKE '%7%[^7]%'
   OR MyField LIKE '%8%[^8]%'
   OR MyField LIKE '%9%[^9]%'

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[AlexCuse]

Not really. There are some improved ways to make use of regular expressions in SQL 2005 is all.

Glad you got it sorted, and thanks for the purple thingy

Ignorance of certain subjects is a great part of wisdom

 

[alexjones]

ESquared - Your final solution is worth a star to me

Alex - I shall do some research on the use of regular expressions in SS2K5 - I do an awful lot of data cleansing and pattern matching is one of the tools I use to do it. Thanks.

 

[AlexCuse]

SandieJ - start here:

http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx

I already have one lengthy post about creating/deploying CLR functions today (way towards the bottom of thread183-1389789) so you can read more about how to use the functions the article talks about there. I plan to make this into an FAQ's at some point.

Ignorance of certain subjects is a great part of wisdom

 

[alexjones]

Alex - Thanks for the references. I've got them saved. Looking forward to your faq.

 

[ESquared]

/* The following for SQL 2005
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', '1'
RECONFIGURE
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
*/

GO
CREATE FUNCTION NewRegExpObj (@Pattern varchar(8000))
RETURNS int
AS
BEGIN
   DECLARE @RegExpObj int
   EXEC sp_OACreate 'VBScript.RegExp', @RegExpObj OUTPUT
   EXEC sp_OASetProperty @RegExpObj, 'Global', 'True'
   EXEC sp_OASetProperty @RegExpObj, 'Pattern', @Pattern
   RETURN @RegExpObj
END
GO
CREATE FUNCTION RegExMatch (@RegExpObj int, @StringToMatch varchar(8000))
RETURNS bit
AS
BEGIN
   DECLARE @Matches bit
   EXEC sp_OAMethod @RegExpObj, 'Test', @Matches OUTPUT, @StringToMatch
   RETURN @Matches
END
GO

DECLARE @R int
SET @R = dbo.NewRegExpObj ('^(7+|8+|9+)$')
SELECT * FROM MyTable WHERE dbo.RegExMatch(@R, n) = 0
GO
SELECT * FROM MyTable WHERE n LIKE '%[7-9]%' OR n LIKE '%7%[^7]%' OR n LIKE '%8%[^8]%' OR n LIKE '%9%[^9]%'

The regular expression code works, but here are the performance numbers for a table with about 9700 rows in it:

[tt] CPU Duration
RegExp 4781 28019
LIKE 31 329[/tt]

So you can see it's about 100 times slower and more CPU intensive than just using LIKE. If you had some really really complicated rules though, this could end up being a good way to do something.

Be aware when using the sp_OA procs that if the thing you call crashes, it takes down the server since it's executing in the server's address space. (That's not always true, if you are running an out-of-process server, an .exe like Word or Excel you are okay. But in this case, the RegExp stuff is just a dll which runs in the process space of the server.)

Debugging these can be difficult. I had to search long and hard to put together a function which would give me proper error messages when something didn't work with an sp_OA proc.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[alexjones]

ESquared -

Thanks for the SS2K5 material. I will take great care in trying something like this. (I really like your LIKE stuff, though.

 

[ESquared]

No, let me make it clear, that code above will work in SQL 2000 as well. It's extremely slow using OLE Automation.

In SQL 2005 one would use CLR along the lines of

http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx

.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]