SUBSTR/INSTR Help.

[markfcuk]

Hi There,

I was wondering if someone could help me with my transition from MSSQL to Oracle.

I have a field name call rec_supplier which contains supplier names with supplier account numbers seperated by a * like this

REC_SUPPLIER
============
2321*Supplier 1
333*Supplier 2
38584*Supplier 3

I to get rid of the Account number and * to just leave supplier name. I tried this ;

SELECT substr(rec_supplier, instr('rec_supplier', '*',1,1)) as tidyname

but it doesn't work.

Please help!

 

[dstxaix]

If REC_SUPPLIER will only contain number and * and then just words or spaces then you could try the following

select TRANSLATE(rec_supplier, '*0123456789', ' ') as tidyname

I am sure some of the gurus here may have a better solution

 

[markfcuk]

Oops sorry i should have checked my post first!!

I only want to keep the company name (which could contain numbers i.e. 365 electrical)

So I want everything after the 1st *

Thanks for your quick reply.

 

[LKBrwnDBA]

You are on the right track:

SELECT substr(rec_supplier, instr('rec_supplier', '*')+1) as tidyname
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops, typo:

SELECT substr(rec_supplier, instr(rec_supplier,'*')+1) as tidyname
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[markfcuk]

fantastic!! that works perfectly.

While I'm here can I ask for help with formatting a number?

stock_value is returned as 7834.3847847987

Can i format this to two decimal place or as a money field £7,834.38?

I tried this but I get an error.

SELECT substr(rec_supplier, instr(rec_supplier,'*')+1) as tidyname, tosum( TTL_OH * avg_cost) as stock_value, to_number(stock_value, '99,999.99') from mtpm

Thanks

 

[markfcuk]

Thanks LKBrwnDBA.

How can reverse that to just show the supplier number, i.e. everything before the star?

Thanks

 

[SantaMufasa]

How can reverse that to just show the supplier number, i.e. everything before the star?

SELECT substr(rec_supplier, 1,instr(rec_supplier,'*')-1) as tidyname

I tried this but I get an error.

Would you like us to guess what error you are receiving? <grin> How about a clue?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

FOR number formatting try the TO_CHAR() function:

SQL> Select to_char(7834.3847847987,'999G999D99','NLS_NUMERIC_CHARACTERS = '',.''') from dual
  2  /

TO_CHAR(783
-----------
   7.834,38

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

....And with currency:

SQL> Select to_char(7834.3847847987,'L999G999D99'
  2        ,'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''£'' ')
  3   from dual
  4 /

TO_CHAR(7834.38478479
---------------------
            £7.834,38

PS: You can set these NLS parameters at the database and/or session level.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb