SQL to get only string with leading numbers

Question

I think this is fairly simple. I'd like to only return the string values that contact leading numbers in my query results.

For example:

003 - Preliminary Examination Plan  
005 - Coordination  
1000a - Balance sheet
Advertising  
Amortization  
Partnerships

Would like to get:

003 - Preliminary Examination Plan  
005 - Coordination  
1000a - Balance sheet

This code gave me zero results. How do I check if the leading numbers contain digits and return the rest of string?

select distinct AIssue
from SQLIssue
where regexp_like( AIssue, '^[[:digit:]]*$' )
order by AIssue

Which database system are you using? SQL Server? If so, the version number may also affect the answer. — Daniel Renshaw, May 24 '12 at 15:43
This is actually a fairly rare database system called Livelink which uses an oracle backend but it uses standard sql to query. — BvilleBullet, May 24 '12 at 15:52

NPE · Accepted Answer · 2012-05-24T15:50:57.460

6

Your current regex reqiures the string to consist entirely of digits. Try the following:

where regexp_like( AIssue, '^[[:digit:]].*$' )

(note the added dot).

To elaborate, . matches any character, and * means "repeat the previous term zero or more times".

Thus, the original regex says "zero or more digits", whereas the above regex says "a digit followed by zero or more of any characters.

edit: A shorter version of the above regex has been suggested by @mellamokb in the comments:

where regexp_like( AIssue, '^[[:digit:]]' )

edited May 24 '12 at 15:50

answered May 24 '12 at 15:45

NPE

This worked thanks so much! Just curious what is the signifigance of the added dot? – BvilleBullet May 24 '12 at 15:50
@BvilleBullet: The dot means "any character", and the asterisk is the repetition operator. I've expanded the answer. – NPE May 24 '12 at 15:53
1

Would it be more efficient just to match `^[[:digit:]]`? It seems to work: http://www.sqlfiddle.com/#!4/4b9ae/5 – mellamokb May 24 '12 at 15:53
@mellamokb: Good idea, thanks. I've included that in the answer. – NPE May 24 '12 at 15:55
works both methods get the correct results. Thanks for the explanation as well. – BvilleBullet May 24 '12 at 15:58

score 1 · Answer 2 · answered May 24 '12 at 15:45

1

If you're using SQL Server, try this:

select distinct Issue
 from SQLIssue
 where AIssue LIKE '[0-9]%'
 order by AIssue

See the documentation for LIKE

answered May 24 '12 at 15:45

Daniel Renshaw

I tried this also before posting but it gave zero results I would assume because the entire string doesnt translate to a number. – BvilleBullet May 24 '12 at 15:52
1

The character range in `LIKE` clause, e.g., `[0-9]`, is a SQL Server-specific (and a couple other RDBMS's) feature: http://stackoverflow.com/questions/712580/list-of-special-characters-for-sql-like-clause. Since you're using an Oracle-based product, the query doesn't work. – mellamokb May 24 '12 at 15:56

score 1 · Answer 3 · answered May 24 '12 at 15:55

1

Another solution, but one that doesn't involve regular expressions:

select distinct Issue
from SQLIssue
where substr(AIssue, 1, 1) between '0' and '9'
order by AIssue

answered May 24 '12 at 15:55

Gordon Linoff

3 Answers3