Home » SQL & PL/SQL » SQL & PL/SQL » How to strip of characters
How to strip of characters [message #31242] Thu, 17 June 2004 12:51 Go to next message
Suchita
Messages: 13
Registered: May 2002
Junior Member
Hi,

  I have some user_id's like 'SKYBLUE/user1@abc.com' and some users like 'user2@abc.com' . I want to strip of SKYBLUE word from the first user_id and it should not strip of any characters from the second user_id.

Here is the input..

user_id

--------

SKYBLUE/user1@abc.com

user2@abc.com

user3@abc.com

SKYBLUE/user4@abc.com

I want output like this..

user_id

--------

user1@abc.com

user2@abc.com

user3@abc.com

user4@abc.com

Can you please help.

Thanks
Re: How to strip off characters [message #31243 is a reply to message #31242] Thu, 17 June 2004 13:21 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
SQL> CREATE TABLE t (x VARCHAR2(30));
 
Table created.
 
SQL> INSERT INTO t VALUES ('SKYBLUE/user1@abc.com');
SQL> INSERT INTO t VALUES ('user2@abc.com');
SQL> INSERT INTO t VALUES ('user3@abc.com');
SQL> INSERT INTO t VALUES ('SKYBLUE/user4@abc.com');
SQL> SELECT x
  2  FROM   t
  3  /
 
X
------------------------------
SKYBLUE/user1@abc.com
user2@abc.com
user3@abc.com
SKYBLUE/user4@abc.com
 
SQL> <font color=blue>UPDATE t</font>
  2  <font color=blue>SET    x = SUBSTR(t.x</font>
  3  <font color=blue>           ,      INSTR(t.x,'/') + 1)</font>
  4  <font color=blue>WHERE  INSTR(t.x,'/') > 0</font>
  5  /
 
2 rows updated.
 
SQL> SELECT x
  2  FROM   t
  3  /
 
X
------------------------------
user1@abc.com
user2@abc.com
user3@abc.com
user4@abc.com
 
SQL>
Re: How to strip off characters [message #31244 is a reply to message #31243] Thu, 17 June 2004 14:10 Go to previous messageGo to next message
Suchita
Messages: 13
Registered: May 2002
Junior Member
Thanks a lot!!. You are really fast!! I never used to get answers this fast..

-Suchita
Re: How to strip of characters [message #31246 is a reply to message #31242] Thu, 17 June 2004 20:54 Go to previous messageGo to next message
Vinodha Kaleeswaran
Messages: 21
Registered: June 2004
Junior Member
Using a single line query
-------------------------
select instr(&p_string , '/'),
length(&p_string) ,
substr(&p_string , -1 * (length(&p_string) - instr(&p_string , '/') ) )
from dual
Re: How to strip of characters [message #203894 is a reply to message #31242] Thu, 16 November 2006 20:54 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
hi all..

what about if i want to remove '_bp28_sabah.data'

from this set of data

bill_recc_charges_bp28_sabah.data
cust_acct_status_bp28_sabah.data
cust_pay_adj_bp28_sabah.data
cust_acct_charges_bp28_sabah.data
cust_pay_adj_bp28_sabah.data
bill_recc_charges_bp28_sabah.data
cust_acct_status_bp28_sabah.data
bill_nrecc_charges_bp28_sabah.data
bill_serv_charges_bp28_sabah.data
bill_nrecc_charges_bp28_sabah.data
cust_acct_status_bp28_sabah.data

I've tried to apply the above solution but it substr it from the front.

How do I substr from back?

Thank you very much
Re: How to strip of characters [message #203922 is a reply to message #203894] Thu, 16 November 2006 23:40 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
try using like this query

SELECT REPLACE ('bill_recc_charges_bp28_sabah.data', '_bp28_sabah.data', '')
FROM DUAL;
Re: How to strip of characters [message #203935 is a reply to message #203922] Fri, 17 November 2006 00:27 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
pravin3032 wrote on Fri, 17 November 2006 13:40
try using like this query

SELECT REPLACE ('bill_recc_charges_bp28_sabah.data', '_bp28_sabah.data', '')
FROM DUAL;


wow!!

thanks a lot pravin!!!

so now.. i want to extend my question such that is there any way that i can use a placeholder for this..

this is because there are all 10 bps altogether i.e. bp01, bp04, bp07,.....,bp25 and bp28.

so, how do i set it so that all those 10 bps would be replaced in one shot!

i've tried..
SELECT distinct REPLACE (file_name, '_bp%_sabah.data', '') FROM rev_load_tbl

but it didn't work! Sad

thanks in advanced
Re: How to strip of characters [message #203938 is a reply to message #31242] Fri, 17 November 2006 00:31 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
regular expressions only supported in oracle10g onwards right?

my oracle is oracle9i.
Re: How to strip of characters [message #203940 is a reply to message #31242] Fri, 17 November 2006 00:33 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
one more question, can i put several other strings to be replaced to NULL in one query?
Re: How to strip of characters [message #203943 is a reply to message #31242] Fri, 17 November 2006 00:37 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
another question..

is that possible to remove string which starts with some pattern.

for example in this case, all strings that need to be removed is starting with '_bp%'

so, is it possible?
Re: How to strip of characters [message #203963 is a reply to message #203935] Fri, 17 November 2006 02:06 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
i am surprise !
my solution raise so many questions to you.
check the query below it will solve ur prob.

[
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as test

SQL>
SQL> SELECT file_name FROM rev_load_tbl ;

file_name
--------------------------------------------------
cust_pay_adj_bp10_sabah.data
cust_acct_charges_bp28_sabah.data
cust_pay_adj_bp28_sabah.data
cust_acct_status_bp30_sabah.data
bill_recc_charges_bp28_sabah.data
cust_acct_status_bp28_sabah.data
bill_nrecc_charges_bp40_sabah.data
bill_serv_charges_bp28_sabah.data
bill_nrecc_charges_bp28_sabah.data
cust_acct_status_bp28_sabah.data

10 rows selected

SQL>
SQL>
SQL> SELECT SUBSTR (file_name, 1, (INSTR (file_name, '_bp') - 1))
FROM rev_load_tbl ;

SUBSTR(file_name,1,(INSTR(file_name,'_BP
--------------------------------------------------
cust_pay_adj
cust_acct_charges
cust_pay_adj
cust_acct_status
bill_recc_charges
cust_acct_status
bill_nrecc_charges
bill_serv_charges
bill_nrecc_charges
cust_acct_status

10 rows selected

SQL>
]

regards
pravin.
Re: How to strip of characters [message #203967 is a reply to message #31242] Fri, 17 November 2006 02:25 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
thanks a lot again pravin. really appreciate.

honestly i don't rally understand your latest query but i believe that is the result that i want.

but now, let me just straight to the point i.e. the case that i am facing right now.

actually inside the rev_load_tbl there are merely filenames with two sets of prefix. The 1st one which ends something like _bp%_sabah.data, while the other one is 2006%.

the table is basically contains 3 main columns i.e. bill_period, file_name and sum_of_rows. so, my mission is actually to count(sum_of_rows) for this 6 tables group by month. bill_period is in the format of 'yyyymmbp'

so, i just want to take the 1st part of the filename which indicates the table_name:

bill_nrecc_charges
bill_recc_charges
bill_serv_charges
cust_acct_charges
cust_acct_status
cust_pay_adj

so, i need to use the function decode right? which is something like this
select substr(BLPERIOD,1,6), <function> as "table_name", count(NUM_OF_ROWS) 
from rev_load_tbl;


so, how is that? since i cannot use placeholder in decode.. Sad

i'm so sorry if i'm bothering you so much.

thanks so much anyway.

Re: How to strip of characters [message #203970 is a reply to message #31242] Fri, 17 November 2006 02:32 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
well... i'm so sorry pravin..

actually by using your latest query i don't need to decode anymore.. thank God.

maybe i should bring another discussion on how to decode column with some search pattern.

I'm so sorry again pravin.

And thank you so much!!!

I'll try to understand the usage of instr.. if I can't understand, you don't mind to teach me right? Wink
Re: How to strip of characters [message #203978 is a reply to message #203970] Fri, 17 November 2006 03:22 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi,

Can you provide here..
1. sample data from all three column of that table
2. output data how u want?

regards
pravin.
Re: How to strip of characters [message #205396 is a reply to message #31242] Fri, 24 November 2006 08:41 Go to previous messageGo to next message
pravisri
Messages: 10
Registered: March 2005
Location: Manchester
Junior Member
The best SQL for the 1st issue is as follows

select SUBSTR(string_col,INSTR(string_col,'/') +1, LENGTH(string_col) - INSTR(string_col,'/') from TABLENAME
Re: How to strip of characters [message #205398 is a reply to message #31242] Fri, 24 November 2006 08:55 Go to previous messageGo to next message
pravisri
Messages: 10
Registered: March 2005
Location: Manchester
Junior Member
The best SQL for

what about if i want to remove '_bp28_sabah.data'

from this set of data

bill_recc_charges_bp28_sabah.data cust_acct_status_bp28_sabah.data
cust_pay_adj_bp28_sabah.data

is as follows

Assume a table KSR is created with one column string_col.


SELECT SUBSTR(string_col,1,(INSTR( string_col,'_bp28_sabah.data')) -1 ) from KSR

Re: How to strip of characters [message #205912 is a reply to message #203978] Tue, 28 November 2006 02:23 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
pravin3032 wrote on Fri, 17 November 2006 17:22
hi,

Can you provide here..
1. sample data from all three column of that table
2. output data how u want?

regards
pravin.


thanks pravin.

well, my previous problem has been solved. thank you so much.

but now, i have more complex query for the same table. basically, the table has 3 main columns i.e blperiod, filename and num_of_rows.

here is the sample data for the column filename:
CIN_BILLED_NONRECURR_CHARGES.20061001
CIN_BILLED_NONRECURR_CHARGES.20061101
CIN_BILLED_NONRECURR_CHARGES.20061201
CIN_BILLED_NONRECURR_CHARGES.20060101
TLX_CUST_ACCT_PAY_ADJ.20060301
TLX_CUST_ACCT_PAY_ADJ.20060401
TLX_CUST_ACCT_PAY_ADJ.20060501
TLX_CUST_ACCT_PAY_ADJ.20060601
MAY_CUST_ACCT_PAY_ADJ.20060401
MAY_CUST_ACCT_PAY_ADJ.20060501
MAY_CUST_ACCT_PAY_ADJ.20060601
MAY_CUST_ACCT_PAY_ADJ.20060701
CUST_ACCT_PAY_ADJ.200602-ICAMS
CUST_ACCT_PAY_ADJ.200603-ICAMS
CUST_ACCT_PAY_ADJ.200604-ICAMS
CUST_ACCT_PAY_ADJ.200605-ICAMS
CUST_ACCT_PAY_ADJ.200606-ICAMS
BILLED_NONRECURR_CHARGES.200610-WAMS
BILLED_RECURR_CHARGES.200610-WAMS
BILLED_SERVICE_CHARGES.200610-WAMS
CUSTOMER_ACCT_CHARGES.200609-WAMS
CUSTOMER_ACCT_CHARGES.200610-WAMS
CUST_ACCT_PAY_ADJ.200610-WAMS
CUST_ACCT_STATUS.200610-WAMS
IBBILLED_NONRECURR_CHARGES.200610-WAMS
IBBILLED_NONRECURR_CHARGES.200611-WAMS
IBBILLED_RECURR_CHARGES.200610-WAMS
IBBILLED_RECURR_CHARGES.200611-WAMS


So, my mission is to display statistics of num_of_rows which is grouped by 'MAY', 'TLX', 'CIN', 'WAMS', 'IBWAMS' and 'ICAMS'.

The output should be something like this:
SOURCE TABLE                             01         02         03         04         
------ ------------------------- ---------- ---------- ---------- ---------- 
COINS  BILLED_NONRECURR_CHARGES         654        230        215        285        
       BILLED_RECURR_CHARGES          49342      12870      13182      16020      
       BILLED_SERVICE_CHARGES         12275       6357       6615       7113       
       CUSTOMER_ACCT_CHARGES           2850        571        642        738        
       CUST_ACCT_PAY_ADJ                818        839       1103        932        
       CUST_ACCT_STATUS                4571       4567       4627       4677       

MAYPAC BILLED_NONRECURR_CHARGES           2          0          3         
       BILLED_RECURR_CHARGES           5477       5467       5481       5457       
       BILLED_SERVICE_CHARGES          1591       1587       1580       1587       
       CUSTOMER_ACCT_CHARGES           1172       1170       1162       1169       
       CUST_ACCT_PAY_ADJ               1085       1049       1068       1081       
       CUST_ACCT_STATUS                2065       2052       2052       2049       

TELEX  BILLED_NONRECURR_CHARGES           0          4          7          
       BILLED_RECURR_CHARGES           3426       3402       3365       3305       
       BILLED_SERVICE_CHARGES          1271       1321       1274       1234       
       CUSTOMER_ACCT_CHARGES           1219       1269       1222       1182       
       CUST_ACCT_PAY_ADJ                911        890        823        873        
       CUST_ACCT_STATUS                1828       1825       1777       1769   


Actually the above output is the one which I've already managed to solved by using this script:

column TABLE format a25
column SOURCE format a6
break on SOURCE skip 1
set linesize 200
set pagesize 36
SELECT distinct 
decode(substr(file_name,1,3), 'MAY', 'MAYPAC', 'CIN', 'COINS',  'TLX', 'TELEX', 'NONE') "SOURCE",
substr(file_name,5, (INSTR (file_name, '.200') -5)) "TABLE",
sum(decode (substr(file_name,-4,2), 01, num_of_rows, 000000000)) "01",
sum(decode (substr(file_name,-4,2), 02, num_of_rows, 000000000)) "02",
sum(decode (substr(file_name,-4,2), 03, num_of_rows, 000000000)) "03",
sum(decode (substr(file_name,-4,2), 04, num_of_rows, 000000000)) "04",
sum(decode (substr(file_name,-4,2), 05, num_of_rows, 000000000)) "05",
sum(decode (substr(file_name,-4,2), 06, num_of_rows, 000000000)) "06",
sum(decode (substr(file_name,-4,2), 07, num_of_rows, 000000000)) "07",
sum(decode (substr(file_name,-4,2), 08, num_of_rows, 000000000)) "08",
sum(decode (substr(file_name,-4,2), 09, num_of_rows, 000000000)) "09",
sum(decode (substr(file_name,-4,2), 10, num_of_rows, 000000000)) "10",
sum(decode (substr(file_name,-4,2), 11, num_of_rows, 000000000)) "11",
sum(decode (substr(file_name,-4,2), 12, num_of_rows, 000000000)) "12"
from rev_load_tbl 
where substr(file_name,1,3) in ('MAY', 'CIN', 'TLX') and substr(file_name,-8,4) = '2006'
group by substr(file_name,1,3), substr(file_name,5, (INSTR (file_name, '.200') -5));


But how do I combine all of them together by using decode and using search pattern like '%test%'?
Re: How to strip of characters [message #205914 is a reply to message #31242] Tue, 28 November 2006 02:27 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
I'm sorry for not be able to explain my problem clearly. My English is not that good and I'm sort of blank right now.

Anyway, I hope you really understand my point here. For file_name starting like 'MAY', 'CIN', 'TLX' it's easy to cater by using substr. But do I cut out those ICAMS and WAMS? And please also note that WAMS is actually divided into two, one of them has prefix-IB in front.

Thank you.
Re: How to strip of characters [message #205970 is a reply to message #205914] Tue, 28 November 2006 04:29 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi aimy

you may find this query useful here..

SELECT DECODE (SUBSTR (FILE_NAME, 1, 3),
               'MAY', 'MAYPAC',
               'CIN', 'COINS',
               'TLX', 'TELEX',
               'NONE'
              ) "SOURCE",
       (CASE
           WHEN (   FILE_NAME LIKE '%ICAMS%'
                 OR FILE_NAME LIKE '%WAMS%')
              THEN SUBSTR (FILE_NAME, 1, (INSTR (FILE_NAME, '.200') - 1))
           ELSE SUBSTR (FILE_NAME, 5, (INSTR (FILE_NAME, '.200') - 5))
        END
       ) "Table"
FROM    REV_LOAD_TBL;



regards
pravin

[Updated on: Tue, 28 November 2006 04:31]

Report message to a moderator

Re: How to strip of characters [message #206118 is a reply to message #205970] Tue, 28 November 2006 18:40 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
pravin3032 wrote on Tue, 28 November 2006 18:29
hi aimy

you may find this query useful here..

SELECT DECODE (SUBSTR (FILE_NAME, 1, 3),
               'MAY', 'MAYPAC',
               'CIN', 'COINS',
               'TLX', 'TELEX',
               'NONE'
              ) "SOURCE",
       (CASE
           WHEN (   FILE_NAME LIKE '%ICAMS%'
                 OR FILE_NAME LIKE '%WAMS%')
              THEN SUBSTR (FILE_NAME, 1, (INSTR (FILE_NAME, '.200') - 1))
           ELSE SUBSTR (FILE_NAME, 5, (INSTR (FILE_NAME, '.200') - 5))
        END
       ) "Table"
FROM    REV_LOAD_TBL;



regards
pravin

Thanks pravin for help.

But that does not solve the issue. Actually, in addition to 'MAYPAC', 'TELEX' and 'COINS' as the source, I want another 3 sources i.e. 'WAMS', 'IBWAMS' and 'ICAMS'. So, how do I decode them altogether in one script since 'WAMS', 'IBWAMS' and 'ICAMS' cannot be easily substr to get the prefix.

I've tried something like
SELECT 
decode(file_name), 
like 'IB%', 'IBWAMS', 
like '%WAMS', 'WAMS',  
like '%ICAMS', 'ICAMS', 
'NONE') "SOURCE" 
from rev_load_tbl;

But as you might already guessed, I cannot use placeholders together with DECODE.

So, CASE might be a solution here, it's just that I'm not sure how do I do that together with decode.

I'll try. In the meantime, I hope you can try to help me. Wink

Thanks a lot.
Re: How to strip of characters [message #206120 is a reply to message #31242] Tue, 28 November 2006 19:46 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Well, I've figured it out finally! Thanks.. Wink
select distinct (CASE
           WHEN ( FILE_NAME LIKE 'IB%WAMS') THEN 'IBWAMS'
		   WHEN ( FILE_NAME LIKE '%ICAMS') THEN 'ICAMS'
           ELSE 'WAMS' 
		end) "SOURCE"
	FROM REV_LOAD_TBL
	where file_name like '%-%';

But now how do I calculate the num_of_rows by months since I need to decode num_of_rows differently to get the month:
for both 'IBWAMS' and 'WAMS' --> substr(file_name,-7,2)
whereas for 'ICAMS' --> substr(file_name,-8,2)

Arghhhhh.... Sad
Re: How to strip of characters [message #206386 is a reply to message #31242] Wed, 29 November 2006 18:39 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
I've tried using this query.. it seems to work and not work.. Sad

column TABLE format a26
column SOURCE format a6
break on SOURCE skip 1
set linesize 200
set pagesize 36
select distinct 
(CASE
	WHEN ( FILE_NAME LIKE 'IB%WAMS') THEN 'IBWAMS'
	WHEN ( FILE_NAME LIKE '%ICAMS') THEN 'ICAMS'
	ELSE 'WAMS' 
end) as "SOURCE",
substr(file_name,1, (INSTR (file_name, '.200') -1)) "TABLE",
(CASE
	WHEN ( FILE_NAME LIKE '%WAMS') THEN sum(decode (substr(file_name,-7,2), '10', num_of_rows, 000000000))
	ELSE sum(decode (substr(file_name,-8,2), '10', num_of_rows, 000000000))
end)  "10"
FROM REV_LOAD_TBL where file_name like '%-%' and file_name like '%2006%'
group by file_name, substr(file_name,1, (INSTR (file_name, '.200') -1));

The problem is that how do I group it by IBWAMS, ICAMS and WAMS? Since I cannot use the CASE statement together with group by. so I can only group by file_name. Sad
Re: How to strip of characters [message #206457 is a reply to message #206386] Thu, 30 November 2006 02:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm confused - why can't you group by CASE.
It works just fine for me.
SQL> select case when col = 1 then 1 else 9 end  col
  2        ,sum(val)
  3  from (select 1 col, 2 val from dual union all
  4        select 1 col, 3 val from dual union all
  5        select 2 col, 1 val from dual union all
  6        select 3 col, 2 val from dual union all
  7        select 4 col, 3 val from dual)
  8  group by case when col = 1 then 1 else 9 end;

                           COL                       SUM(VAL)
------------------------------ ------------------------------
                             1                              5
                             9                              6
Previous Topic: create foreign key trigger
Next Topic: Removal of comma from CSV file while creating External Table
Goto Forum:
  


Current Time: Sat Dec 03 08:25:00 CST 2016

Total time taken to generate the page: 0.26236 seconds