We help IT Professionals succeed at work.

Remove leading numbers from varchar2

eis
eis asked
on
53 Views
Last Modified: 2016-02-05
Is there an easy way to remove all leading numerical and space values from the beginning part of a varchar2?

Example:
Before: "21952 Hello2 World"
After: "Hello2 World"

Thanks,
- Dan
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Try this (there is a space character after the 9):
regexp_substr(column_name,'^[^0-9 ]+)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
oops... forget that.  It isn't right.
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
regexp_replace(column_name,'^[0-9 ]+')   --- the ltrim above should be more efficient though

or, if you want to replace any non-alphabetic leading characters
this works if you might have other undesirable characters and you don't want to have to specify each of them, instead specify the start of what you want to keep, rather than the list of what to get rid of


regexp_replace(column_name,'^[^A-Za-z]+')
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Try this:
regexp_replace(column_name,'^[0-9 ]+')
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>The ORACLE ltrim() function?

That seems even simpler!!!!

Much better than mine!!!
eis

Author

Commented:
This worked great! Thanks for your help
- Dan
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.