Interesting findings with string pattern matching using [specifier]...or is it just sysobjects turning the tables?

I found some interesting results today, when using the "like" command
combined with pattern matching for a range of numbers
eg. searches with the use of [1-100] or [A-Z]
aka [specifier] or [rangespec] as the symbol

Please see below - I will comment as I go along.

The QUERY in plain english!
"Get me only the TABLE names from sysobjects where the names are like
"table_" and
ending in a number. eg [1-150]


ATTEMPT 1 ---select * from sysobjects where name like "table%" and
type = "U"
COMMENT --- didn't quite work for me as I ONLY want ones ending in
numbers - expecting 16 of them

sysobjects.name
table_80
table_business -- bad result
table_24
table_69
table_99
table_9
table_29
table_6
table_15
table_20
table_39
table_57
table_58
table_90
table_96
table_105
table_107
table_type -- bad result


ATTEMPT 2 ---select * from sysobjects where name like "table_[0-150]%"
and type = "U"
COMMENT --- Somehow the rest of the tables are left out!!! I expect
16
--- leaving the % out... returns zero rows.

table_15
table_57
table_58
table_105
table_107

ATTEMPT 3 ---"table_%[0-150]%"
COMMENT --- again no luck - too little rows!
and leaving the % out... returns zero rows.

table_80
table_15
table_20
table_57
table_58
table_90
table_105
table_107


ATTEMPT 4 ---"table_%[0-150]"
COMMENT --- again no luck - too little rows! -- spot the different
positions of the % sign yet!?

name
table_80
table_15
table_20
table_90
table_105


ATTEMPT 5 ---"table_[0-n]%"
COMMENT --- again no luck - too many rows! -- spot the different
positions of the % sign!?
--- leaving the % out... returns 2 rows, table 6
and 9

table_80
table_business
table_24
table_69
table_99
table_9
table_29
table_6
table_15
table_20
table_39
table_57
table_58
table_90
table_96
table_105
table_107



ATTEMPT 6 ---"table_[0-N]"
COMMENT --- with out % gives only 2 rows - crazy!

table_9
table_6


THE ANSWER - and the code that got me the list like I wanted it...
"table_[0-N]%" OR "table_[1-N]%"
COMMENT --- Note the UPPERCASE (N)


select * from sysobjects where name like "table_[0-N]%" and type =
"U"

sysobjects.name
table_80
table_24
table_69
table_99
table_9
table_29
table_6
table_15
table_20
table_39
table_57
table_58
table_90
table_96
table_105
table_107

I am running --- Adaptive Server Enterprise/12.5.4/EBF 14064 ESD#3/P/
Sun_svr4/OS 5.8/ase1254/2030/32- bit/FBO/Sat Oct 14 05:28:53 2006

Let me know what you think!
L

.

Relevant Pages
Loading