Search code examples
sqloracle-databasevarchar

Oracle, how to match leading zero number in varchar?

I have some code record in Oracle DB

for example:

A00105XYZ

CC000036QWE

How to write the criteria, if users input A105XYZ, CC36QWE, these records can still be searched?

Solution

  • You'll probably want to use a regular expression:

    SELECT STR, REGEXP_REPLACE(STR,'([^[:digit:]]*)(0*)(.*)','\1\3') NEW_STR 
FROM 
(SELECT 'A00105XYZ' STR FROM DUAL UNION
 SELECT 'CC000036QWE' STR FROM DUAL UNION
 SELECT 'FD403T' STR FROM DUAL UNION
 SELECT '000000010' STR FROM DUAL)


╔═════════════╦═════════╗
║     STR     ║ NEW_STR ║
╠═════════════╬═════════╣
║ 000000010   ║ 10      ║
║ A00105XYZ   ║ A105XYZ ║
║ CC000036QWE ║ CC36QWE ║
║ FD403T      ║ FD403T  ║
╚═════════════╩═════════╝