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.
As per the oracle rules column name of field can't start with Numeric value .
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.
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.
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.
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:]]')
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')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With