UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Remove Part Of A String (oracle)    
 
   
rowiga
post Aug 22 2016, 01:20 PM
Post#1



Posts: 471
Joined: 1-February 05
From: State of Confusion


I have a column in an oracle database (11.2.0.4.0) that I want to strip out part of a string. The string is something like this:

"R94523-33_1430_M"

I want to update the column using a where clause that will remove the two underscores and everything in between resulting in "R94523-33M"

The table is arinvt and the column is itemno

I looked through the code archives in the string area but didn't find anything or I didn't know what I was looking at...lol.

Can someone suggest a method to get this done?

Any help appreciated.

Thanks,
Bob
Go to the top of the page
 
Doug Steele
post Aug 22 2016, 01:42 PM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Warning: I'm not an Oracle expert, but from what I can see in the Oracle documentation, INSTR(FieldName, '_', 1, 1) should give you the position of the first underscore character, while INSTR(FieldName, '_', 1, 2) should give you the position of the second underscore character.

Once you know those positions, you should be able to use SUBSTR to get what you want:

CODE
SUBSTR(FieldName, 1, INSTR(FieldName, '_', 1, 1) - 1) || SUBSTR(FieldName, INSTR(FieldName, '_', 1, 2) + 1)
Go to the top of the page
 
cheekybuddha
post Aug 22 2016, 01:43 PM
Post#3


UtterAccess VIP
Posts: 9,281
Joined: 6-December 03
From: Telegraph Hill


Hi Bob,

You should be able to do this using the followingP-SQL string functions:

SUBSTR()
INSTR()
CONCAT()

Use INSTR() to locate the positions of the underscores.

Use SUBSTR() to chop out the relevant parts.

Use CONCAT() to join them together again

Try first running a SELECT statement to check you're going to get the desired results:
CODE
SELECT
  itemno "OriginalItemNo",
  INSTR(itemno, '_') "FirstUnderscorePos",
  INSTR(itemno, '_', , 2) "SecondUnderscorePos",
  SUBSTR(itemno, 1, INSTR(itemno, '_')) "FirstPortion",
  SUBSTR(itemno, INSTR(itemno, '_', , 2) + 1) "SecondPortion",
  CONCAT(SUBSTR(itemno, 1, INSTR(itemno, '_')), SUBSTR(itemno, INSTR(itemno, '_', , 2) + 1)) "NewItemNo"
FROM arinvt;


If so, then run the UPDATE query:
CODE
UPDATE arinvt
SET itemno = CONCAT(SUBSTR(itemno, 1, INSTR(itemno, '_')), SUBSTR(itemno, INSTR(itemno, '_', , 2) + 1));


(Note: untested; also, I have never used Oracle!)

hth,

d
Go to the top of the page
 
rowiga
post Aug 22 2016, 03:27 PM
Post#4



Posts: 471
Joined: 1-February 05
From: State of Confusion


That was great help guys!

Here's what I ended up with.

CODE
SELECT
  itemno "OriginalItemNo",
  INSTR(itemno, '_') "FirstUnderscorePos",
  INSTR(itemno, '_',1,2) "SecondUnderscorePos",
  SUBSTR(itemno, 1, INSTR(itemno, '_')) "FirstPortion",
  SUBSTR(itemno, INSTR(itemno, '_',1,2) + 1) "SecondPortion",
  SUBSTR(itemno, 1, INSTR(itemno, '_', 1, 1) - 1) || SUBSTR(itemno, INSTR(itemno, '_', 1, 2) + 1) "NewItemNo"
FROM arinvt


I attached a shot of the results

Thanks a bunch thumbup.gif
Bob
Attached File(s)
Attached File  UA_Help_Results.JPG ( 32.53K )Number of downloads: 3
 
Go to the top of the page
 
cheekybuddha
post Aug 22 2016, 03:32 PM
Post#5


UtterAccess VIP
Posts: 9,281
Joined: 6-December 03
From: Telegraph Hill


I made a small mistake with grabbing the first portion - it should read SUBSTR(itemno, 1, INSTR(itemno, '_') - 1) "FirstPortion"

But Doug got it right, so since you used his expression for the NewItemNo, you got the desired result!

thumbup.gif @Doug

d
Go to the top of the page
 
rowiga
post Aug 23 2016, 08:20 AM
Post#6



Posts: 471
Joined: 1-February 05
From: State of Confusion


Thanks, Doug. I meant to clean that up before I posted but forgot. I'm new to oracle myself. I liked seeing how everything worked together to get the final result which makes me more familiar with the concept and not just be a code copier.
Bob
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:55 AM