Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

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.
0
daledog9
Asked:
daledog9
  • 4
  • 2
1 Solution
 
sdstuberCommented:
UPDATE yourtable
   SET yourstring      =
           CASE
               WHEN REGEXP_LIKE(yourstring, '^[a-zA-Z]') THEN SUBSTR(yourstring, 1, 1)
               ELSE SUBSTR(yourstring, 1, 2)
           END
0
 
sdstuberCommented:
what if you have

1A2B?

Do you want 1A  or just 1?
0
 
daledog9Author 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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sdstuberCommented:
ok, then my first post with the case should do it
0
 
daledog9Author Commented:
Thank you,

The code that you provided above has produced my desired result. You really know your SQL code!  Greatly appreciated.
0
 
sdstuberCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now