Home » SQL & PL/SQL » SQL & PL/SQL » Translate function (10g)
Translate function [message #588625] Thu, 27 June 2013 03:23 Go to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi All,

Can you please give a suggestion or tip how to handle the below scenarios in a single query.
Below is the Piece of code i added which gives me a temporary solution..but i need to make this as permanent solution.I should not hardcode the value like 3 as the postion of '-'.

 
 --If my srting Comes like '19-A M.R.S. 1803' then i use this query  
      
  SELECT INSTR(str_in,'-', 1)  
    INTO lv_num      
    FROM DUAL;
  
   IF lv_num = 3
   THEN   
   select TRANSLATE( '19-A M.R.S. 1803', 'a, ''.+<>()[]', 'a||||||||||||') from dual;
                           
    --and if my string str_in comes like '2013-ODH-2007' then i should this use this query 
   ELSE
   
   select TRANSLATE( '2013-ODH-2007' , 'a, ''.-+<>()[]','a||||||||||||') from dual;        

   END If;

Re: Translate function [message #588627 is a reply to message #588625] Thu, 27 June 2013 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It might help if you described what is the purpose of what you are doing. There's the input (strings you posted). What result do you expect, generally speaking?

P.S. What I meant to say is: this is what you do now:
SQL> select TRANSLATE( '19-A M.R.S. 1803', 'a, ''.+<>()[]', 'a||||||||||||') from dual union
  2  select TRANSLATE( '2013-ODH-2007' , 'a, ''.-+<>()[]','a||||||||||||') from dual;

TRANSLATE('19-AM
----------------
19-A|M|R|S||1803
2013|ODH|2007

SQL>

What rule should be applied? The only one is that - if hyphen (-) is the third character, you don't want to substitute it with a pipe (|). Is that correct? If so, CASE might be an option here.
SQL> with test as
  2    (select '19-A M.R.S. 1803' col from dual union
  3     select '2013-ODH-2007'    col from dual
  4    )
  5  select
  6    case when instr(col, '-', 1) = 3 then
  7              translate(col, 'a, ''.+<>()[]', 'a||||||||||||')
  8         else translate(col, 'a, ''.-+<>()[]','a||||||||||||')
  9    end result
 10  from test;

RESULT
----------------
19-A|M|R|S||1803
2013|ODH|2007

SQL>

[Updated on: Thu, 27 June 2013 03:31]

Report message to a moderator

Re: Translate function [message #588630 is a reply to message #588627] Thu, 27 June 2013 04:06 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Thanks little foot for your reply..
But my need is i should not hard code my position of value for '-' as 3..this should work generic for all my string which i pass..
Re: Translate function [message #588632 is a reply to message #588630] Thu, 27 June 2013 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Translate function [message #588633 is a reply to message #588630] Thu, 27 June 2013 04:10 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's why I asked for further explanation (which you didn't provide yet). Why do you care about hyphen position? What difference does it make here: 19-A and here: 2013-ODH? What final result do you expect? Can you describe the algorithm that leads from input to output?
Re: Translate function [message #588648 is a reply to message #588633] Thu, 27 June 2013 04:54 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi My data will be like this
  create table test (input varchar2(100));

insert into test values ('29 M.R.S. 1311-A(5-A)');
insert into test values ('17-A M.R.S. 602(2)(C)');
insert into test values ('2013-PDOMOHIO-2064');
insert into test values ('19-A M.R.S. 1803 '); 

commit;


My out put for this should be like this
29|M|R|S||1311|A|5|A|
17-A|M|R|S||602|2||C|
2013|PDOMOHIO|2064
19-A|M|R|S||1803
Hope now it's clear
Re: Translate function [message #588657 is a reply to message #588648] Thu, 27 June 2013 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not really. Why is "17-A" different from "2013-PDOMOHIO"? How do you know that 17-A should stay as is, while 2013-PDOMOHIO should be translated to 2013|PDOMOHIO?
Re: Translate function [message #588659 is a reply to message #588657] Thu, 27 June 2013 05:14 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
The scratch point is this and based on this output we are creating a search key which identifies the records from DB to take exact matching records.and the search key logic is entirely a different concept.
Re: Translate function [message #588663 is a reply to message #588659] Thu, 27 June 2013 05:23 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't understand it, sorry. Regarding sample data you posted, hyphen on the third position makes the difference, but you don't want to hardcode that position. I asked for explanation (my previous message) but you didn't provide it, so I have no idea what to do. Someone smarter than me might assist, I'm afraid I can not.
Re: Translate function [message #588671 is a reply to message #588663] Thu, 27 June 2013 05:36 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
oh! ok Sad ,but anyways thanks a lot Littlefoot.
Re: Translate function [message #588672 is a reply to message #588671] Thu, 27 June 2013 05:44 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See if this makes any sense:
SQL> with test as
  2    (select '29 M.R.S. 1311-A(5-A)' col from dual union
  3     select '17-A M.R.S. 602(2)(C)' col from dual union
  4     select '2013-PDOMOHIO-2064'    col from dual union
  5     select '19-A M.R.S. 1803 '     col from dual
  6    )
  7  select
  8    col,
  9    regexp_replace(col, '\W', '|', decode(instr(col, ' '), 0, 1, instr(col, ' '))) result
 10  from test;

COL                   RESULT
--------------------- ------------------------------
17-A M.R.S. 602(2)(C) 17-A|M|R|S||602|2||C|
19-A M.R.S. 1803      19-A|M|R|S||1803|
2013-PDOMOHIO-2064    2013|PDOMOHIO|2064
29 M.R.S. 1311-A(5-A) 29|M|R|S||1311|A|5|A|

SQL>
Re: Translate function [message #588674 is a reply to message #588672] Thu, 27 June 2013 05:57 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi ,Thanks a lot littlefoot.. can you explain me what '\W' does..I searched in this link http://www.orafaq.com/node/2404 but i couldn't get it
Re: Translate function [message #588676 is a reply to message #588672] Thu, 27 June 2013 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
regexp_replace(col, '\W', '|', decode(instr(col, ' '), 0, 1, instr(col, ' ')))


I don't know if it is correct from OP point of view but it is a nice one.

Regards
Michel
Re: Translate function [message #588677 is a reply to message #588676] Thu, 27 June 2013 06:17 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
\W is a non-word character (anything but alphanumerics), as it seems that you are converting these into pipes.

INSTR - based on miserable 4 sample input values - searches for a space character. If there's none, convert all \W characters into pipes. Otherwise, convert these that follow the first space character. Certainly, I'm not sure whether it is correct or not, but that's what I managed to understand.
Re: Translate function [message #588678 is a reply to message #588677] Thu, 27 June 2013 06:28 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Thanks a lot for your explanation and I am testing it with more inputs. and i have a doudt that if i contains a special charater in my string for example "M.R.S. tit. 13-A sec. 714 (1)" will it work .Actually this is a rare input ..we need to handle this also..
Re: Translate function [message #588691 is a reply to message #588678] Thu, 27 June 2013 07:43 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi, I tried with the above case it is failing with this above statement. This didn't help me i need to remove only '-' character not any other special character.
Re: Translate function [message #588692 is a reply to message #588691] Thu, 27 June 2013 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
userora wrote on Thu, 27 June 2013 13:43
This didn't help me i need to remove only '-' character not any other special character.


That directly contradicts what you said you wanted earlier.
Again - you need to explain the logic you want applied. It would also help if you gave your definition of special characters.
Re: Translate function [message #588788 is a reply to message #588692] Fri, 28 June 2013 07:52 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi,
I have provided all possible scenarios as input which i receive from data with correct output.

create table test (input varchar2(100));

insert into test values ('29 M.R.S. 1311-A(5-A)');
insert into test values ('17-A M.R.S. 602(2)(C)');
insert into test values ('2013-PDOMOHIO-2064');
insert into test values ('19-A M.R.S. 1803 '); 
insert into test values ('writ and/or petition 10-06-2003');
insert into test values ('writ and/or petition');
insert into test values ('pet. ref'd, untimely filed');
insert into test values ('pet. cert.');
insert into test values ('[2005] Y.J. No. 3');

commit;

OUTPUT 

29|M|R|S||1311|A|5|A|
17-A|M|R|S||602|2||C|
2013|PDOMOHIO|2064
19-A|M|R|S||1803
writ|and/or|petition|10|06|2003
writ|and/or|petition
pet||ref|d||untimely|filed
pet||cert|
|2005||Y|J||No||3


Re: Translate function [message #588789 is a reply to message #588788] Fri, 28 June 2013 07:55 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to explain the rules in words.
I have no idea why 19-A stays as 19-A but 2013-PDOMOHIO becomes 2013|PDOMOHIO

Without knowing why they are treated differently there is no way I (or anybody else) can write code to satisfy your requirements.
Re: Translate function [message #588791 is a reply to message #588788] Fri, 28 June 2013 07:59 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
userora wrote on Fri, 28 June 2013 08:52
Hi,
I have provided all possible scenarios as input which i receive from data with correct output.


I just don't get it. Why sometimes spaces, why sometimes not? Why does the '-' sometimes split, sometimes not?
You still have not given an explanation, only bad examples that contradict each other.
How about you explain in full sentences why each of the examples you have given, have the given output?
Re: Translate function [message #588793 is a reply to message #588791] Fri, 28 June 2013 08:01 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi i am saying my output should look like this ..not the output i got .
Re: Translate function [message #588794 is a reply to message #588793] Fri, 28 June 2013 08:02 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
unhelpful.
Re: Translate function [message #588799 is a reply to message #588793] Fri, 28 June 2013 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
userora wrote on Fri, 28 June 2013 14:01
Hi i am saying my output should look like this ..not the output i got .


We know that.

You need to explain the rules in words.
You need to explain the rules in words.
You need to explain the rules in words.
You need to explain the rules in words.
You need to explain the rules in words.

I don't generally stoop to repeating myself like that but you are really trying my patience. Multiple people have now told you to explain the rules properly in words. We've told you that we can't work out the rules from just comparing the input and expected output.
You can post a million different inputs and outputs and it will not change the fact that we can't work out what rules to apply from just comparing the input and output.
For this problem, the only way for us to be able to help you is if you explain the rules in words.

So stop posting inputs and outputs and spend some time writing down the rules. In fact you should already have them written down somewhere - if you don't how on earth are you going to test that any solution is correct?
Re: Translate function [message #588805 is a reply to message #588799] Fri, 28 June 2013 09:14 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
can you please explain me what rules you need in words..I already explained in my previous message like this

"The scratch point is this and based on this output we are creating a search key which identifies the records from DB to take exact matching records,and the search key logic is entirely a different concept."

Do you need me to explain the full functionality in this Smile i hope it's impossible .

Re: Translate function [message #588807 is a reply to message #588805] Fri, 28 June 2013 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
A repeat posting of something that made no sense the first time posted does NOT clarify anything.
Re: Translate function [message #588810 is a reply to message #588807] Fri, 28 June 2013 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
userora wrote on Fri, 28 June 2013 15:14
can you please explain me what rules you need in words..

The rules that determine how to get the output from the given input.
Or if you prefer - the rules that determine which special characters are converted into | and which aren't.
That's so simple that I'm really not sure why I'm explaining it again. (the rules may not be simple, but the request that you give them is)

userora wrote on Fri, 28 June 2013 15:14

I already explained in my previous message like this

No you haven't, not once.

userora wrote on Fri, 28 June 2013 15:14

"The scratch point is this and based on this output we are creating a search key which identifies the records from DB to take exact matching records,and the search key logic is entirely a different concept."

What's a scratch point? Do you really think that sentence contains any meaningful information about the rules? It really doesn't.
Re: Translate function [message #589020 is a reply to message #588810] Mon, 01 July 2013 08:17 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Hi ,

Sorry for late reply..Actually mine is law related project.

Just i will explain what i want to apply in my search.

We have some law number with section like below data. From which i have to filter some rule number and then i have to apply '|' simple at end of the each and every word of a data. So that i can pass the filtered numeric value to my plsql procedure which was already written.


1. i have to apply '|' symbol for each and every word including space.
2. i have to eliminate all special character. Which means instead of special character i have to use | as a separator.

Ex. 843 A.2d 1237 output : 843||A|2d||1237
2013-PDOMOHIO-2064 output : 2013|PDOMOHIO|2064
pet. ref'd, untimely filed output : pet||ref|d||untimely|filed

M.R.S. tit. 12 sec. 4252 (3) output : M|R|S||tit||12||sec||4252||3|
{ end with special character '(' so replaceing '|' }

M.R.S. tit. 11 sec. 3-405 output : M|R|S||tit||11||sec||3|405
{ Key word M.R.S. came but section (A to Z) is not appended with rule number.
example 17-A M.R.S. 602(2)(C) }

17-A M.R.S. 602(2)(C) output : 17-A|M|R|S||602|2||C|

3. if i have any M.R.S. value, for that data i have to take rule number with the section(A to Z)
Ex.
M.R.S. tit. 24-A sec. 1521 (3)
output : M|R|S||tit||24-A|sec||1521||3|

24-A is my rule and section. so i have to take as it is.


4. Data which contain M.R.S. key word sometimes have rule and section value as(17-A).
sometimes it won't have section number. like this " data M.R.S. tit. 12 sec. 4252 (3). "


Re: Translate function [message #589021 is a reply to message #589020] Mon, 01 July 2013 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
userora wrote on Mon, 01 July 2013 14:17
24-A is my rule and section. so i have to take as it is.

So in any given bit of data, how do we know that 24-A is a rule and section and not some other bit of data where the - needs to be replaced with a |?

That's what we've been asking for an explanation for from the beginning.
It might be obvious to you at glance, but you know the data, we don't.

So we still don't have a rule that lets us identify which bits of data shouldn't be changed and we still can't give a solution.
Re: Translate function [message #589081 is a reply to message #589021] Tue, 02 July 2013 01:32 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
hi,
Thanks for you reply.

The data does not have M.R.S. keyword, directly we can replace the - as |.
If data have M.R.S. key word, we have to check whether it contains any rule number associated with section number ( means numeric value (0-9) with - and non-numeric value (a-z) ). Sometimes the data with M.R.S. won't have rule number associated with section number.

examples:
17-A M.R.S. 602(2)(C)  
M.R.S. tit. 12 sec. 4252 (3)

17-A M.R.S. 602(2)(C) --> for this 

Rule number and section number will come as 17-A  ,202-C. 
This means that numeric value -(hyphen) non-numeric value.
Width of Numeric value(0-9) will come 1 or more. But the non-numeric value(a-z) will come once.
For example:
17-A  ,202-C, 304-Z like this.

And the section value associated with hyphen and rule number will come before M.R.S(means start position).
example:
17-A M.R.S. 602(2)(C) 17-A|M|R|S||602|2||C|
19-A M.R.S. 1803      19-A|M|R|S||1803| 
29 M.R.S. 1311-A(5-A) 29|M|R|S||1311|A|5|A|

Re: Translate function [message #591365 is a reply to message #589081] Fri, 26 July 2013 18:59 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
It looks like, if the value " M.R.S." is in the string, then anything before that stays as is and, for anything from that point on, all non-alphanumeric characters are replaced with "|". If there are exceptions to that, then you need to explain how to recognize them and how to handle them. Saying that the rule and section need to remain unchanged is insufficient. There needs to be some automated means for identifying what is and is not a rule and section.

SCOTT@orcl12c> column input  format a35
SCOTT@orcl12c> column result format a35
SCOTT@orcl12c> select input,
  2  	    string1 || regexp_replace(string2, '\W', '|') result
  3  from   (select input,
  4  		    substr (input, 1, instr (input, ' M.R.S.') - 1) string1,
  5  		    substr (input, instr (input, ' M.R.S.')) string2
  6  	     from   test)
  7  /

INPUT                               RESULT
----------------------------------- -----------------------------------
29 M.R.S. 1311-A(5-A)               29|M|R|S||1311|A|5|A|
17-A M.R.S. 602(2)(C)               17-A|M|R|S||602|2||C|
2013-PDOMOHIO-2064                  2013|PDOMOHIO|2064
19-A M.R.S. 1803                    19-A|M|R|S||1803|
writ and/or petition 10-06-2003     writ|and|or|petition|10|06|2003
writ and/or petition                writ|and|or|petition
pet. cert.                          pet||cert|
[2005] Y.J. No. 3                   |2005||Y|J||No||3

8 rows selected.

Previous Topic: removing special characters and get desired characters from column values
Next Topic: sql query on range wise
Goto Forum:
  


Current Time: Fri Sep 19 13:38:01 CDT 2014

Total time taken to generate the page: 0.11853 seconds