daledog9
asked on
How do I Substring Character Data based on data values
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
11A2B = 11
102456=10
A2B11 = A
AA2B23 = A
So 1A2B scenerio would never exist
ok, then my first post with the case should do it
ASKER
Thank you,
The code that you provided above has produced my desired result. You really know your SQL code! Greatly appreciated.
The code that you provided above has produced my desired result. You really know your SQL code! Greatly appreciated.
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
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
1A2B?
Do you want 1A or just 1?