We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How do I Substring Character Data based on data values

daledog9
daledog9 asked
on
Medium Priority
807 Views
Last Modified: 2013-12-18
Currently using ETL Tool (Oracle Data Integrator) to move data.  I have a category that needs truncated, it is varchar data with mixed values (numeric, varchar).

If the value starts with a number I need to substring the first two characters
If the value starts with a character I need to substring the first character

How do i write a sql statement for my desired results, doesn't seem to work using IN with %?

Example:

11130 = 11
1075 = 10
AA123 = A
C125 = C

Thank you.
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
what if you have

1A2B?

Do you want 1A  or just 1?

Author

Commented:
Anything starting with a number will be 2 characters, it will always be two numerical and anything starting with a character will always want just the first character

11A2B = 11
102456=10
A2B11 = A
AA2B23 = A

So 1A2B scenerio would never exist
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok, then my first post with the case should do it

Author

Commented:
Thank you,

The code that you provided above has produced my desired result. You really know your SQL code!  Greatly appreciated.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help,  incidentally if you did want to use IN
the case might look something like this...

case
when substr(yourstring,1,1) in (0,1,2,3,4,5,6,7,8,9) then substr(yourstring,1,2)
else substr(yourstring,1,1)
end
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.