Oracle SQL : search column that starts with digits

Question

I'd like to find all the rows which column value begins with a digit.

Its works well with this request :

    WHERE trim(u_ods_val3.ods_itn_PHRSBMO.NO_ART_TECH_OI)    IS NOT NULL
  AND (SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)='0'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='1'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='2 '
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='3'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='4'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='5'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='6'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='7'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='8'
  OR SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1)  ='9')

But it is too long.

Thank you for your help.

use [LIKE](http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm), it is exactly for this — Yaroslav, Oct 01 '12 at 09:20

Nick Krasnov · Answer 1 · 2012-10-02T14:08:35.663

22

Regexp_like would be in handy and much shorter

where regexp_like(trim(col_name), '^[0-9]')

or using character class

where regexp_like(trim(col_name), '^[[:digit:]]')

edited Oct 02 '12 at 14:08

answered Oct 01 '12 at 09:21

Nick Krasnov

26,886
6
61
78

score 2 · Answer 2 · answered Oct 01 '12 at 09:20

2

Try to use in :

WHERE trim(u_ods_val3.ods_itn_PHRSBMO.NO_ART_TECH_OI)    IS NOT NULL
  AND SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1) in ('0','1','2','3','4','5','6','7','8','9')

answered Oct 01 '12 at 09:20

Robert

25,425
8
67
81

score 1 · Answer 3 · answered Oct 01 '12 at 09:28

BETWEEN is all you need! (NOT NULL is implicit in this case!)

WHERE SUBSTR(u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI,0,1) between '0' and '9'

If you have an index on that column, and don't mind the little dirtyness of this solution, you can even speed it up:

WHERE u_ods_val3.ODS_ITN_PHRSBMO.NO_ART_TECH_OI between '0' and '9~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

This assumes that NO_ART_TECH_OI doesn't contain characters with ascii code > 126.

Oracle SQL : search column that starts with digits

3 Answers3