search and extract portion of description 1

[Abslag]

I am trying to 'extract' if that is the correct word, a portion of a description for a field, please see example.

here's my simple statement:
select hla.LOG_ID,
to_char(hl.TIME_STAMP,'mm-dd-yy')Chg_Date,
history_log_api.Get_Description(hla.log_id)Part_Desc,
hla.OLD_VALUE,
hla.NEW_VALUE
from ifsapp.history_log_attribute hla,
ifsapp.history_log hl
where hla.LOG_ID = hl.log_id

IT LOOKS LIKE THIS...
LOG_ID CHANGE_DATE PART_DESC OLD_VALUE NEW_VALUE
568085 06-15-05 Sales Part Number: 22-1000, Site: 1000 14.37 19.74
568086 06-15-05 Sales Part Number: 22-1001, Site: 1000 14.37 19.74
568087 06-15-05 Sales Part Number: 22-1002, Site: 1000 14.37 19.74
568088 06-15-05 Sales Part Number: 22-1003, Site: 1000 14.37 19.74
568089 06-15-05 Sales Part Number: 22-1004, Site: 1000 7.17 19.74
568090 06-15-05 Sales Part Number: 22-1005, Site: 1000 7.17 19.74


From the description field I want to isolate the actual part number without all the other stuff so the field only says 22-1000 or 22-1001 etc...

 

[engineer2100]

select trim(substring(part_desc,length('Sales Part Number: ')+1)) from history_log_attribute

considering that the PART_DESC always starts with 'Sales Part Number: '

Hope this helps!!

 

[Abslag]

Thank you engineer2100, good stuff and I am half way there, I just need to be able to tell it to stop at the comma -1. Any other help on how I can do that?

 

[engineer2100]

oh.. I saw only a part of the request.. let me rewrite the select for you

SELECT TRIM (SUBSTR (PART_DESC,
                      LENGTH ('Sales Part Number: ') + 1,
                     INSTR (SUBSTR (PART_DESC,
                                     LENGTH ('Sales Part Number: ') + 1
                                   ),
                            ',',
                            1
                           )-1
                    )
            )  "PART #"
  FROM PART

again considering a ',' after the Part #

HTH

-Engi

 

[Abslag]

Thanx Engi,
I worked great. Much appreciated!!