Sqlplus Masking?

Sqlplus Masking?

Post by Peter Ka » Sun, 18 Apr 1993 00:21:25



I need to create a view that masks a '=' off the end of a character
string, but not all of the strings have the '='.

For example, if I had:

order_num       part_num
---------       --------
S12345          ABC
S87654          ABC=

I need to mask off the '=' in the second record's part_num so I can
get a sum for 'ABC' that would include the 'ABC=' part.

Any ideas?

Thanks,

Peter Kane

 
 
 

Sqlplus Masking?

Post by rzx2.. » Sat, 17 Apr 1993 22:06:52



>Date: 16 Apr 1993 15:21:25 GMT

>I need to create a view that masks a '=' off the end of a character
>string, but not all of the strings have the '='.

>For example, if I had:

>order_num       part_num
>---------       --------
>S12345          ABC
>S87654          ABC=

>I need to mask off the '=' in the second record's part_num so I can
>get a sum for 'ABC' that would include the 'ABC=' part.

>Any ideas?

This worked:
  1  CREATE VIEW NOEQUALS AS
  2  SELECT ORDER_NUM,
  3    SUBSTR(PART_NUM,1,DECODE(INSTR(PART_NUM,'='),0,LENGTH(PART_NUM),
  4                      INSTR(PART_NUM,'=')-1))
  5         PART_NOEQUAL
  6* FROM X

View created.

SQL> SELECT PART_NOEQUAL, COUNT(ORDER_NUM)
  2  FROM NOEQUALS
  3  GROUP BY PART_NOEQUAL
  4  /

PART_NOE COUNT(ORDER_NUM)
-------- ----------------
ABC                     2

========================================================================
                                           ,-------+
Tom Harleman                               |       |
Steering Committee Member                  |       |
Paradigm Consulting, Inc.                  | INOUG |
11080 Willowmere Drive                     | Indiana Oracle Users Group
Indianapolis, IN  46280                    |       |
USA                                        |      _+
                                           /   _,'
Compuserve: 72072,2122                    /_,-'
========================================================================

 
 
 

Sqlplus Masking?

Post by Roderick Manal » Sat, 24 Apr 1993 16:12:20


|> I need to create a view that masks a '=' off the end of a character
|> string, but not all of the strings have the '='.
|>
|> For example, if I had:
|>
|> order_num       part_num
|> ---------       --------
|> S12345          ABC
|> S87654          ABC=
|>
|>
|> I need to mask off the '=' in the second record's part_num so I can
|> get a sum for 'ABC' that would include the 'ABC=' part.
|>
|> Any ideas?

rtrim(part_num,'=') should be enough unless your string contains more
than one trailing '='.

Roderick Manalac
Worldwide Support

 
 
 

Sqlplus Masking?

Post by Dave Harris » Sun, 25 Apr 1993 01:18:38


Surely the easiest way to mask off unwanted trailing characters is to use
the RTRIM function, e.g.  RTRIM(part_num,'=')
Dave Harrison
 
 
 

1. Sqlplus Masking


Subject: Re: Sqlplus Masking?

Try rtrim(char,[set])
page 4-17 in sql language reference Manual
example from book
select rtrim('turner xxXxx','x') example
from dual;
example
-------
turner xxX


|> I need to create a view that masks a '=' off the end of a character
|> string, but not all of the strings have the '='.
|>
|> For example, if I had:
|>
|> order_num       part_num
|> ---------       --------
|> S12345          ABC
|> S87654          ABC=
|>
|>
|> I need to mask off the '=' in the second record's part_num so I can
|> get a sum for 'ABC' that would include the 'ABC=' part.
|>
|> Any ideas?
|>
|> Thanks,
|>
|> Peter Kane

--
John Hawkins       || #define disclaimer(X) fprintf(stderr," X\n");
Northern Telecom   || Voice: 615-734-4468                 /* Able was I, ere */
200 Athens Way     || Fax:   615-734-4771                 /* I saw Elba      */

2. 64 ppp connections

3. sql*forms, format mask, date field

4. session request to *SMBSERVER failed

5. Forms 4.0 Char Format Mask

6. Prtblk help in MWC

7. Forms 4.0 Format mask suck!

8. Where can I get Emacs for my Sparc (mail order tape ~$200 I heard)

9. SQL*Forms Numeric Format Masks

10. SQL*FORMS40 field format mask question

11. Format Masks in R2.5

12. DATE FORMAT MASK

13. Setting NLS_DATE_FORMAT mask