Determine if DB name has numerics

Question

Determine if DB name has numerics

MothInTheMachine

Hall of Fame

Points: 3251
More actions
June 19, 2019 at 2:05 pm

#3655280
I am writing a script to move through specific databases in our server and if the name ends in 2 digits then I run a select statement in it. The following returns a 1 if the ISNUMERIC returns true
```
select  sd.name, ISNUMERIC(RIGHT(sd.name,2)
from    sys.sysdatabases sd
Where   sd.name not in ('master','tempdb','model','msdb')
```
However when I run that in the WHERE clause it doesn't return the db names in the resultset.
```
select  sd.name
from    sys.sysdatabases sd
Where   sd.name not in ('master','tempdb','model','msdb') AND ISNUMERIC(RIGHT(sd.name,2)) != 0;
```
How can I find the databases whose name ends in 2 numerics?

EDIT: This does work. THe failure was on another part of the query. I'm obviously not awake yet 🙁
- This topic was modified 5 years, 5 months ago by MothInTheMachine.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

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

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Here's another way which is slightly more robust (because IsNumeric() can return unexpected results).

SELECT d.name
FROM sys.databases d
WHERE d.database_id > 4
      AND PATINDEX('%[0-9][0-9]', d.name) > 0;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99130 More actions · Answer 2

Phil's answer is the way to go here. To expand on his comment about ISNUMERIC, for this specific scenario database names that end in something like .5 and -9 would also meet the requirements of your WHERE, even though they don't have 2 digits at the end of their name. For example, note what the below returns:

SELECT V.DBName,
       RIGHT(V.DBName,2) AS Right2,
       ISNUMERIC(RIGHT(V.DBName,2)) AS Numerical
FROM (VALUES(N'MyDatabase123'),(N'TestDB.1'),(N'Trial-7'),(N'DB 4'),(N'YourDB0.'))V(DBName);

ISNUMERIC did used to get used to check if the value "could" be interpreted as a numerical data type, however, it had it quirks and gave false answers. For example ISNUMERIC('') will return 0, however, '' can be converted to a numerical data type. On the other hand '.' would return 1, but can't be converted. You also have cases where ISNUMERIC('1.0') would return 1, however, CONVERT(int, '1.0') would generate an error (trying to convert to a decimal would work though). For checking to see if a value can be converted to a numerical datatype, you want TRY_CONVERT and TRY_CAST (which are both available in 2012+)

For what you want, using PATINDEX or LIKE, with 2 single character ranges at the end will work 100% of the time and not provide incorrect results.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101108 More actions · Answer 3

I'd use RIGHT rather than PATINDEX, just because I think's it mildly clearer:

WHERE RIGHT(name, 2) LIKE '[0-9][0-9]'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".