Finding strings with 3 consecutive numbers

Question

Finding strings with 3 consecutive numbers

JackCarrington

Mr or Mrs. 500

Points: 519
More actions
August 13, 2018 at 9:24 am

#365929

Hi all,
As per my project described in : https://www.sqlservercentral.com/Forums/1983777/Huge-number-of-tables (well worth following!!)
I want to find all tables (from sys.tables) that have (any) 3 consecutive number in their name. All the tables below would qualify:
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5 -- as it contains 285
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5AUDIT-- as it contains 285
ANNOTATION287BFAD3E8494CE6897D3A735955260D-- as it contains 287
ANNOTATION287BFAD3E8494CE6897D3A735955260DAUDIT -- as it contains 287
ANNOTATION31F7028741B44CB097A4C5BA30C0525A -- as it contains 702

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

JackCarrington - Monday, August 13, 2018 9:24 AM
Hi all,
As per my project described in : https://www.sqlservercentral.com/Forums/1983777/Huge-number-of-tables (well worth following!!)
I want to find all tables (from sys.tables) that have (any) 3 consecutive number in their name. All the tables below would qualify:
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5 -- as it contains 285
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5AUDIT-- as it contains 285
ANNOTATION287BFAD3E8494CE6897D3A735955260D-- as it contains 287
ANNOTATION287BFAD3E8494CE6897D3A735955260DAUDIT -- as it contains 287
ANNOTATION31F7028741B44CB097A4C5BA30C0525A -- as it contains 702

Should be pretty straight forward.

SELECT * FROM sys.tables WHERE name LIKE '%[0-9][0-9][0-9]%'

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

JackCarrington - Monday, August 13, 2018 9:24 AM
Hi all,
As per my project described in : https://www.sqlservercentral.com/Forums/1983777/Huge-number-of-tables (well worth following!!)
I want to find all tables (from sys.tables) that have (any) 3 consecutive number in their name. All the tables below would qualify:
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5 -- as it contains 285
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5AUDIT-- as it contains 285
ANNOTATION287BFAD3E8494CE6897D3A735955260D-- as it contains 287
ANNOTATION287BFAD3E8494CE6897D3A735955260DAUDIT -- as it contains 287
ANNOTATION31F7028741B44CB097A4C5BA30C0525A -- as it contains 702

Just curious, what is the rational behind that logic?
😎

The names look like string converted GUIDS, are they serialized?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

How about this:
DECLARE @TestTab TABLE(TableName NVARCHAR(128)); INSERT INTO @TestTab VALUES ('ANNOTATION21DDDF2855CB40F689CF3466208EE0F5') ,('ANNOTATION21DDDF2855CB40F689CF3466208EE0F5AUDIT') ,('ANNOTATION287BFAD3E8494CE6897D3A735955260D') ,('ANNOTATION287BFAD3E8494CE6897D3A735955260DAUDIT') ,('ANNOTATION31F7028741B44CB097A4C5BA30C0525A') ,('ANNOTATION31F70B87B1B44CB09DA4C5BA30C05C5A');

SELECT * FROM @TestTab AS [tt] WHERE [tt].[TableName] LIKE '%[0-9][0-9][0-9]%';

JackCarrington Mr or Mrs. 500 Points: 519 More actions · Answer 4

Eirikur Eiriksson - Monday, August 13, 2018 9:40 AM
JackCarrington - Monday, August 13, 2018 9:24 AM
Hi all,
As per my project described in : https://www.sqlservercentral.com/Forums/1983777/Huge-number-of-tables (well worth following!!)
I want to find all tables (from sys.tables) that have (any) 3 consecutive number in their name. All the tables below would qualify:
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5 -- as it contains 285
ANNOTATION21DDDF2855CB40F689CF3466208EE0F5AUDIT-- as it contains 285
ANNOTATION287BFAD3E8494CE6897D3A735955260D-- as it contains 287
ANNOTATION287BFAD3E8494CE6897D3A735955260DAUDIT -- as it contains 287
ANNOTATION31F7028741B44CB097A4C5BA30C0525A -- as it contains 702
Just curious, what is the rational behind that logic?
😎
The names look like string converted GUIDS, are they serialized?

I will address your questions in that thread (for the sake of thread purity, ie one topic, one thread 🙂 )