Geek asks and answers

Oracle SQL: Search column starting with numbers

I would like to find all rows whose column value starts with a digit.

Works well with this query:

    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.

+5
source share
3 answers

Regexp_like would be convenient and much shorter

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

or using a character class

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

Try using 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')
+1
source

BETWEEN- that’s all you need! ( NOT NULLimplicitly in this case!)

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

If you have a pointer to this column and are not against the slight pollution of this solution, you can even speed it up:

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

It is assumed that NO_ART_TECH_OI does not contain characters with an ascii code> 126.

+1
source

More articles:


All Articles