Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL : search column that starts with digits

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.

like image 869
astrotouf Avatar asked Oct 01 '12 09:10

astrotouf


People also ask

Can Oracle column name start with number?

As per the oracle rules column name of field can't start with Numeric value .

How do you check if a column is alphanumeric in Oracle?

Answer: To test a string for alphanumeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle. The string value that you are testing. This function will return a null value if string1 is alphanumeric.

How do I find the number of digits in SQL?

SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

What is Regexp_like in Oracle?

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE . This condition evaluates strings using characters as defined by the input character set.


3 Answers

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:]]')
like image 189
Nick Krasnov Avatar answered Oct 21 '22 13:10

Nick Krasnov


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')
like image 25
Robert Avatar answered Oct 21 '22 13:10

Robert


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.

like image 33
Erich Kitzmueller Avatar answered Oct 21 '22 12:10

Erich Kitzmueller