Better Method to Detect Record with Multiple/All 0's?

Question

Post reply

Better Method to Detect Record with Multiple/All 0's?

DB_Newbie2007

SSCarpal Tunnel

Points: 4438
More actions
February 17, 2015 at 7:59 am

#294698

This is probably more of a "RegEx" kind of question.... I am wondering if anyone has a suggestion as to a better method to detect records with one or more 0's, other than hard coding all of the potential values. For example, I need to detect account duplicates where the duplicate contains multiple/all 0's in the "MasterAccount" column:
DECLARE @account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )
INSERT INTO @account
SELECT 'ABC','0000000ABC' UNION
SELECT 'DEF','0000000DEF' UNION
SELECT 'ABC','0000000000' UNION ---- unwanted duplicate
SELECT 'DEF','00000' UNION ---- unwanted duplicate
SELECT 'GHI','0'
SELECT A.AccountNo, A.MasterAccount
, '||' AS [ ] ----==== this is just a data separator for readability/comparison
, B.AccountNo AS AccountNoB, B.MasterAccount AS MasterAccountB
FROM @account A
INNER JOIN @account B
ON A.AccountNo = B.AccountNo
WHERE A.MasterAccount <> B.MasterAccount
AND B.MasterAccount IN ('0','00','000','0000','00000','000000','0000000','00000000','000000000','0000000000') ---- Is there a better method than this?
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

Try this

AND B.MasterAccount NOT LIKE '%[^0]%'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

Another possibility, non-sargable:

and replace(B.MasterAccount,'0','')=''

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Dwain Camps SSC Guru Points: 86913 More actions · Answer 3

DECLARE @account TABLE(AccountNo varchar(3), MasterAccount varchar(10) ) ;

INSERT INTO @account

SELECT 'ABC','0000000ABC' UNION

SELECT 'DEF','0000000DEF' UNION

SELECT 'ABC','0000000000' UNION ---- unwanted duplicate

SELECT 'DEF','00000' UNION ---- unwanted duplicate

SELECT 'GHI','0' ;

SELECT *

FROM @account

WHERE MasterAccount <> REPLICATE('0', LEN(MasterAccount))

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86913 More actions · Answer 4

dwain.c (2/17/2015)
DECLARE @account TABLE(AccountNo varchar(3), MasterAccount varchar(10) ) ; INSERT INTO @account SELECT 'ABC','0000000ABC' UNION SELECT 'DEF','0000000DEF' UNION SELECT 'ABC','0000000000' UNION ---- unwanted duplicate SELECT 'DEF','00000' UNION ---- unwanted duplicate SELECT 'GHI','0' ; SELECT * FROM @account WHERE MasterAccount <> REPLICATE('0', LEN(MasterAccount))

Note that this could be made SARGable by creating a computed-persisted column on LEN(MasterAccount) and INDEXing on it and MasterAccount.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 5

For fun, here is a 2012 and later method using TRY_CONVERT

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )

INSERT INTO @account

SELECT 'ABC','0000000ABC' UNION ALL

SELECT 'DEF','0000000DEF' UNION ALL

SELECT 'ABC','0000000000' UNION ALL ---- unwanted duplicate

SELECT 'DEF','00000' UNION ALL ---- unwanted duplicate

SELECT 'GHI','0' UNION ALL

SELECT 'GHI','00' UNION ALL

SELECT 'GHI','000' UNION ALL

SELECT 'GHI','0000' UNION ALL

SELECT 'GHI','00000' UNION ALL

SELECT 'GHI','000000' UNION ALL

SELECT 'GHI','0000000' UNION ALL

SELECT 'GHI','00000000' UNION ALL

SELECT 'GHI','000000000' UNION ALL

SELECT 'GHI','0000000000';

;WITH BASE_DATA AS

(

SELECT

AC.AccountNo

,AC.MasterAccount

,ISNULL(TRY_CONVERT(BIGINT,AC.MasterAccount),1) AS IS_VALID

FROM @account AC

)

SELECT

*

FROM BASE_DATA BD

WHERE BD.IS_VALID > 0

;

Results

AccountNo MasterAccount IS_VALID

--------- ------------- ---------

ABC 0000000ABC 1

DEF 0000000DEF 1