Home » SQL & PL/SQL » SQL & PL/SQL » Trim Out Special Character...
icon6.gif  Trim Out Special Character... [message #328078] Wed, 18 June 2008 21:18 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Does anyone knows how do I extract the value of 39 out of 103 39' 2.0"

Actually that is sample of value inside a column called LODI_VALUE from my table. The objective here is to get the degree value and the minute value.

For the degree value, I have tried..
column lodi_value format a20
column degree format a10
column minute format a10
SELECT LODI_VALUE,
 substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
 substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1, 
 instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
FROM  
  LOCATIONS , 
  LOCATION_DETAIL_INSTANCE 
WHERE 
  (LOCATION_DETAIL_INSTANCE.LODI_NAME LIKE '%LONGITUDE%')   
   AND LOCATION_DETAIL_INSTANCE.LODI_LOCN_ID = LOCN_ID;

and it works fine i.e I will get the 103 value returned.

However, for the minute I could not get the result.

Please help me.

Thank you very much.


By the way, for the degree symbol , on my PC it was displayed as question mark symbol, '?'
Re: Trim Out Special Character... [message #328084 is a reply to message #328078] Wed, 18 June 2008 22:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above


It would be most helpful if you provided the DML to INSERT test data into the table whose DDL you also provide.
icon9.gif  Re: Trim Out Special Character... [message #328100 is a reply to message #328084] Wed, 18 June 2008 23:21 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Thu, 19 June 2008 11:29
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above


It would be most helpful if you provided the DML to INSERT test data into the table whose DDL you also provide.

I'm sorry.

I thought it would be enough by just using the sample value that I've provided.

So, here it is:

CREATE TABLE
create table aimy_longitude (lodi_value varchar2(36));

VALUES
insert into aimy_longitude values ('103 39'' 2.0"');
insert into aimy_longitude values ('100 25'' 23.2"');
insert into aimy_longitude values ('101 42'' 5.0"');
insert into aimy_longitude values ('104 7'' 50.9"');
insert into aimy_longitude values ('100 12'' 28.1"');
insert into aimy_longitude values ('100 12'' 28.1"');
insert into aimy_longitude values ('101 46'' 42.0"');
insert into aimy_longitude values ('100 22'' 18.0"');
insert into aimy_longitude values ('100 19'' 52.7"');
insert into aimy_longitude values ('101 43'' 55.0"');
insert into aimy_longitude values ('101 43'' 55.0"');
insert into aimy_longitude values ('96 45'' 27.0"');
insert into aimy_longitude values ('1 143'' 15.0"');

Thank you very much.
Re: Trim Out Special Character... [message #328101 is a reply to message #328078] Wed, 18 June 2008 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, for the minute I could not get the result.

What do you actually get?
What did you try?

Regards
Michel
icon7.gif  Re: Trim Out Special Character... [message #328102 is a reply to message #328078] Wed, 18 June 2008 23:23 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
So now the quety would be like this...
SELECT LODI_VALUE,
 substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
 substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1, 
 instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
FROM aimy_longitude


Thank you.
Re: Trim Out Special Character... [message #328103 is a reply to message #328101] Wed, 18 June 2008 23:26 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Michel Cadot wrote on Thu, 19 June 2008 12:21
Quote:
However, for the minute I could not get the result.

What do you actually get?
What did you try?

Regards
Michel


After I create the sample table as above..

Now I couldn't get any value for degree anymore, Sad instead the minute value is now returned but still it is incorrect.. Confused
SELECT LODI_VALUE,
 substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
 substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1, 
 instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
FROM aimy_longitude



LODI_VALUE           degree     minute
-------------------- ---------- ----------
103? 39' 2.0"                   103? 39
100? 25' 23.2"                  100? 25
101? 42' 5.0"                   101? 42
104? 7' 50.9"                   104? 7
100? 12' 28.1"                  100? 12
100? 12' 28.1"                  100? 12
101? 46' 42.0"                  101? 46
100? 22' 18.0"                  100? 22
100? 19' 52.7"                  100? 19
101? 43' 55.0"                  101? 43
101? 43' 55.0"                  101? 43
96? 45' 27.0"                   96? 45
1? 143' 15.0"                   1? 143

13 rows selected.

As you can see, the degree symbol '' which I've inserted before is now displayed with '?' symbol.. Mad

[Updated on: Wed, 18 June 2008 23:28]

Report message to a moderator

Re: Trim Out Special Character... [message #328104 is a reply to message #328078] Wed, 18 June 2008 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member

  1  SELECT LODI_VALUE,
  2   substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
  3   substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1,
  4   instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
  5* FROM aimy_longitude
SQL> /

LODI_VALUE                           degree                               minute
------------------------------------ ------------------------------------ ------------------------------------
103? 39' 2.0"                                                             103? 39
100? 25' 23.2"                                                            100? 25
101? 42' 5.0"                                                             101? 42
104? 7' 50.9"                                                             104? 7
100? 12' 28.1"                                                            100? 12
100? 12' 28.1"                                                            100? 12
101? 46' 42.0"                                                            101? 46
100? 22' 18.0"                                                            100? 22
100? 19' 52.7"                                                            100? 19
101? 43' 55.0"                                                            101? 43
101? 43' 55.0"                                                            101? 43

LODI_VALUE                           degree                               minute
------------------------------------ ------------------------------------ ------------------------------------
96? 45' 27.0"                                                             96? 45
1? 143' 15.0"                                                             1? 143

13 rows selected.

SQL> 
Re: Trim Out Special Character... [message #328106 is a reply to message #328104] Wed, 18 June 2008 23:34 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Thu, 19 June 2008 12:30

  1  SELECT LODI_VALUE,
  2   substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
  3   substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1,
  4   instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
  5* FROM aimy_longitude
SQL> /

LODI_VALUE                           degree                               minute
------------------------------------ ------------------------------------ ------------------------------------
103? 39' 2.0"                                                             103? 39
100? 25' 23.2"                                                            100? 25
101? 42' 5.0"                                                             101? 42
104? 7' 50.9"                                                             104? 7
100? 12' 28.1"                                                            100? 12
100? 12' 28.1"                                                            100? 12
101? 46' 42.0"                                                            101? 46
100? 22' 18.0"                                                            100? 22
100? 19' 52.7"                                                            100? 19
101? 43' 55.0"                                                            101? 43
101? 43' 55.0"                                                            101? 43

LODI_VALUE                           degree                               minute
------------------------------------ ------------------------------------ ------------------------------------
96? 45' 27.0"                                                             96? 45
1? 143' 15.0"                                                             1? 143

13 rows selected.

SQL> 


That's not the correct result right?? Sad
Re: Trim Out Special Character... [message #328109 is a reply to message #328078] Wed, 18 June 2008 23:44 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
What does this query return to you?
SELECT LODI_VALUE, DUMP(LODI_VALUE, 1016) FROM aimy_longitude;


[Edit: changed plural to singular]

[Updated on: Wed, 18 June 2008 23:44]

Report message to a moderator

Re: Trim Out Special Character... [message #328112 is a reply to message #328078] Wed, 18 June 2008 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>That's not the correct result right??
Your data.
Your query.
Your results.

You decide the correctness (or not).

why is "degree" empty?
Re: Trim Out Special Character... [message #328124 is a reply to message #328102] Thu, 19 June 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT LODI_VALUE,
  2   substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
  3   substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1, 
  4   instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
  5  FROM aimy_longitude;
LODI_VALUE           degree     minute
-------------------- ---------- ----------
103 39' 2.0"        103         39
100 25' 23.2"       100         25
101 42' 5.0"        101         42
104 7' 50.9"        104         7
100 12' 28.1"       100         12
100 12' 28.1"       100         12
101 46' 42.0"       101         46
100 22' 18.0"       100         22
100 19' 52.7"       100         19
101 43' 55.0"       101         43
101 43' 55.0"       101         43
96 45' 27.0"        96          45
1 143' 15.0"        1           143

13 rows selected.

Quite correct for me.
Now it depends on your character set. Are you sure chr(176) is in yours?

Regards
Michel
Re: Trim Out Special Character... [message #328141 is a reply to message #328109] Thu, 19 June 2008 01:59 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
flyboy wrote on Thu, 19 June 2008 12:44
What does this query return to you?
SELECT LODI_VALUE, DUMP(LODI_VALUE, 1016) FROM aimy_longitude;


[Edit: changed plural to singular]

Thanks..

The results...
LODI_VALUE           dump
-------------------- -------------------------------------------------------------------------------------
103? 39' 2.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,30,33,bf,20,33,39,27,20,32,2e,30,22
100? 25' 23.2"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,30,bf,20,32,35,27,20,32,33,2e,32,22
101? 42' 5.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,30,31,bf,20,34,32,27,20,35,2e,30,22
104? 7' 50.9"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,30,34,bf,20,37,27,20,35,30,2e,39,22
100? 12' 28.1"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,30,bf,20,31,32,27,20,32,38,2e,31,22
100? 12' 28.1"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,30,bf,20,31,32,27,20,32,38,2e,31,22
101? 46' 42.0"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,31,bf,20,34,36,27,20,34,32,2e,30,22
100? 22' 18.0"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,30,bf,20,32,32,27,20,31,38,2e,30,22
100? 19' 52.7"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,30,bf,20,31,39,27,20,35,32,2e,37,22
101? 43' 55.0"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,31,bf,20,34,33,27,20,35,35,2e,30,22
101? 43' 55.0"       Typ=1 Len=14 CharacterSet=WE8ISO8859P1: 31,30,31,bf,20,34,33,27,20,35,35,2e,30,22
96? 45' 27.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 39,36,bf,20,34,35,27,20,32,37,2e,30,22
1? 143' 15.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,bf,20,31,34,33,27,20,31,35,2e,30,22

Re: Trim Out Special Character... [message #328144 is a reply to message #328124] Thu, 19 June 2008 02:14 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Michel Cadot wrote on Thu, 19 June 2008 13:31
SQL> SELECT LODI_VALUE,
  2   substr(LODI_VALUE, 1,instr(LODI_VALUE,chr(176))-1) "degree",
  3   substr(LODI_VALUE, instr(LODI_VALUE,chr(176))+1, 
  4   instr(LODI_VALUE,chr(39)) - instr(LODI_VALUE,chr(176)) -1) "minute"
  5  FROM aimy_longitude;
LODI_VALUE           degree     minute
-------------------- ---------- ----------
103 39' 2.0"        103         39
100 25' 23.2"       100         25
101 42' 5.0"        101         42
104 7' 50.9"        104         7
100 12' 28.1"       100         12
100 12' 28.1"       100         12
101 46' 42.0"       101         46
100 22' 18.0"       100         22
100 19' 52.7"       100         19
101 43' 55.0"       101         43
101 43' 55.0"       101         43
96 45' 27.0"        96          45
1 143' 15.0"        1           143

13 rows selected.

Quite correct for me.
Now it depends on your character set. Are you sure chr(176) is in yours?

Regards
Michel


Thanks Michael.

This is the result that I get..
SQL> select chr(176), chr(39) from dual;

C C
- -
? '

So, actually what should the the exact character set that I should use?

How do I set the character set by the way??

The database is on the UNIX server, IBM AIX.

Thank you.
Re: Trim Out Special Character... [message #328145 is a reply to message #328112] Thu, 19 June 2008 02:16 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Thu, 19 June 2008 12:46
>That's not the correct result right??
Your data.
Your query.
Your results.

You decide the correctness (or not).

why is "degree" empty?

I don't know.

That's what I'm trying to figure out.

Previously, when I tested the script on the original table, it works at least for the degree part.
Re: Trim Out Special Character... [message #328146 is a reply to message #328078] Thu, 19 June 2008 02:19 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Can the character be set temporarily on a script alone like the sqlplus set command?
Re: Trim Out Special Character... [message #328147 is a reply to message #328078] Thu, 19 June 2008 02:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
LODI_VALUE           dump
-------------------- -------------------------------------------------------------------------------------
103? 39' 2.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,30,33,bf,20,33,39,27,20,32,2e,30,22
...

This is the real content of the LODI_VALUE column.
In the 4th position it contains BFh = 191d = inverted question mark.
Whatever tool you used for inserting rows, it did not treat the degree sign correctly.
Re: Trim Out Special Character... [message #328149 is a reply to message #328147] Thu, 19 June 2008 02:29 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
flyboy wrote on Thu, 19 June 2008 15:22
LODI_VALUE           dump
-------------------- -------------------------------------------------------------------------------------
103? 39' 2.0"        Typ=1 Len=13 CharacterSet=WE8ISO8859P1: 31,30,33,bf,20,33,39,27,20,32,2e,30,22
...

This is the real content of the LODI_VALUE column.
In the 4th position it contains BFh = 191d = inverted question mark.
Whatever tool you used for inserting rows, it did not treat the degree sign correctly.

Thanks flyboy.

But sorry I didn't quite get you.

Anyway, FYI I am using the direct sqllplus on the server side (telnet to the UNIX server and run the sqlplus username/password) for both insert and the query.

So, what I have to do now?? It's such a big frustation for me since I could not really get the correct results by applying my sql knowledge on this thing.
Re: Trim Out Special Character... [message #328150 is a reply to message #328106] Thu, 19 June 2008 02:33 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

You can also try this, by finding 1st, 2nd occurrances of blank
space (' ').

select
   substr('103 39'' 2.0" ',1,instr('103 39'' 2.0"',' ')) degree,
   substr('103 39'' 2.0" ', instr('103 39'' 2.0"', ' '),
   (instr('103 39'' 2.0"', ' ', 1, 2)-instr('103 39'' 2.0"', ' '))) minute,
   substr('103 39'' 2.0" ',instr('103 39'' 2.0"',' ',1,2)) secs
from dual


DEGRE MINUT SECS
----- ----- ------
103   39'   2.0"

Regards,

MSMallya
Re: Trim Out Special Character... [message #328152 is a reply to message #328149] Thu, 19 June 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
FYI I am using the direct sqllplus on the server side (telnet to the UNIX server and run the sqlplus username/password) for both insert and the query

It depends on your client session character set defined by NLS_LANG parameter and your server database character set.
Both must support in order to get it in the database, otherwise it is replaced by a replacement character which is often an inverse quotation that your SQL*Plus seems to not be able to display as it converts it to a normal quotation mark.

Quote:
what I have to do now??

First check your database character set supports .
Then check your clients NLS_LANG parameter
Then change all your query with chr(176) by ''
Then update all your current rows to change the "bf" character to ''.

Regards
Michel
Re: Trim Out Special Character... [message #328159 is a reply to message #328149] Thu, 19 June 2008 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the ISO 8859-1 character set (which is what WE8ISO8859P1 is), the degree symbol should be CHR(186)
Re: Trim Out Special Character... [message #328169 is a reply to message #328150] Thu, 19 June 2008 03:59 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
msmallya wrote on Thu, 19 June 2008 15:33
Hi,

You can also try this, by finding 1st, 2nd occurrances of blank
space (' ').

select
   substr('103 39'' 2.0" ',1,instr('103 39'' 2.0"',' ')) degree,
   substr('103 39'' 2.0" ', instr('103 39'' 2.0"', ' '),
   (instr('103 39'' 2.0"', ' ', 1, 2)-instr('103 39'' 2.0"', ' '))) minute,
   substr('103 39'' 2.0" ',instr('103 39'' 2.0"',' ',1,2)) secs
from dual


DEGRE MINUT SECS
----- ----- ------
103   39'   2.0"

Regards,

MSMallya

Thank you so much for your idea!!

It really works!!! Surprised Cool
Re: Trim Out Special Character... [message #328170 is a reply to message #328152] Thu, 19 June 2008 04:00 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Michel Cadot wrote on Thu, 19 June 2008 15:35
Quote:
FYI I am using the direct sqllplus on the server side (telnet to the UNIX server and run the sqlplus username/password) for both insert and the query

It depends on your client session character set defined by NLS_LANG parameter and your server database character set.
Both must support in order to get it in the database, otherwise it is replaced by a replacement character which is often an inverse quotation that your SQL*Plus seems to not be able to display as it converts it to a normal quotation mark.

Quote:
what I have to do now??

First check your database character set supports .
Then check your clients NLS_LANG parameter
Then change all your query with chr(176) by ''
Then update all your current rows to change the "bf" character to ''.

Regards
Michel


Thank you so much. Cool
Re: Trim Out Special Character... [message #328171 is a reply to message #328159] Thu, 19 June 2008 04:03 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
JRowbottom wrote on Thu, 19 June 2008 15:40
In the ISO 8859-1 character set (which is what WE8ISO8859P1 is), the degree symbol should be CHR(186)

Thanks.

Yeah, I've checked..
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8ISO8859P1

But when I select chr(186) from dual; it is still showing the ? symbol, Sad
Re: Trim Out Special Character... [message #328174 is a reply to message #328078] Thu, 19 June 2008 04:10 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
After all the joy I've got from msmallya's idea..

I was beaten again with these two data pattern..

E 101' 24' 32.5"
and 100 39 02 E Confused Confused Confused

For the 1st case, I used rtrim(ltrim(lodi_value, 'ENSW '), 'ENSW ') to replace the lodi_value. And it seems OK for me.

But for the 2nd one.. Oh no!!!! Shocked Shocked Shocked


E 101' 24' 32.5"	101' 24' 32.5"	101	 24	 32.5
100  39  02 E	100  39  02	10		 39  0
104? 1.31? E	104? 1.31?	104		104? 1.31?
103? 03.08? E	103? 03.08?	103		103? 03.08?
103? 00.32? E	103? 00.32?	103		103? 00.32?
100? 44.934? E	100? 44.934?	100		100? 44.934?
102? 13.396? E	102? 13.396?	102		102? 13.396?
100? 53.089? E	100? 53.089?	100		100? 53.089?
100? 36.597? E	100? 36.597?	100		100? 36.597?
104.01? E	104.01?			104.01?
102? 15.12? E	102? 15.12?	102		102? 15.12?
100? 24.225? E	100? 24.225?	100		100? 24.225?
101? 51.45 ? E	101? 51.45 ?	101	 51.4	 
103? 56.37? E	103? 56.37?	103		103? 56.37?
101? 45.918? E	101? 45.918?	101		101? 45.918?
102? 16.412? E	102? 16.412?	102		102? 16.412?


Oh oh!!! That's really crazy!!! Mad Nevermind... Confused
icon6.gif  Re: Trim Out Special Character... [message #328175 is a reply to message #328078] Thu, 19 June 2008 04:14 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Thanks a lot to all of you.

Anyway, now I am still figuring out how to involve the degree symbol and the minute symbol ' with the query.
Re: Trim Out Special Character... [message #328181 is a reply to message #328175] Thu, 19 June 2008 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How about this:
  1  select regexp_substr(lodi_value,'^[^ ]+') deg
  2        ,regexp_substr(lodi_value,' [^ ]+') min
  3        ,regexp_substr(lodi_value,'[^ ]+.$') sec
  4        ,lodi_value
  5  from (select trim(regexp_replace(lodi_value,'[ESWN]',' ')) lodi_value
  6*       from   aimy_longitude)
SQL> /

DEG        MIN        SEC        LODI_VALUE
---------- ---------- ---------- --------------------
103        39'       2.0"       103 39' 2.0"
100        25'       23.2"      100 25' 23.2"
101        42'       5.0"       101 42' 5.0"
104        7'        50.9"      104 7' 50.9"
100        12'       28.1"      100 12' 28.1"
100        12'       28.1"      100 12' 28.1"
101        46'       42.0"      101 46' 42.0"
100        22'       18.0"      100 22' 18.0"
100        19'       52.7"      100 19' 52.7"
101        43'       55.0"      101 43' 55.0"
101        43'       55.0"      101 43' 55.0"

DEG        MIN        SEC        LODI_VALUE
---------- ---------- ---------- --------------------
96         45'       27.0"      96 45' 27.0"
1          143'      15.0"      1 143' 15.0"
Re: Trim Out Special Character... [message #328354 is a reply to message #328181] Thu, 19 June 2008 18:33 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sorry to pile on, just to add another option:

SQL> select lodi_value
  2     , regexp_substr(lodi_value, '[^ ESWN]+', 1, 1) deg
  3     , regexp_substr(lodi_value, '[^ ESWN]+', 1, 2) min
  4     , regexp_substr(lodi_value, '[^ ESWN]+', 1, 3) sec
  5  from aimy_longitude;

LODI_VALUE                     DEG        MIN        SEC
------------------------------ ---------- ---------- ----------
E 103 39' 2.0"                103       39'        2.0"
100 25' E 23.2"               100       25'        23.2"
101 N 42' 5.0"                101       42'        5.0"
104 N 7' 50.9"                104       7'         50.9"
100 E 12' 28.1"               100       12'        28.1"
100 12' E 28.1"               100       12'        28.1"
101 W 46' 42.0"               101       46'        42.0"
100 S 22' 18.0"               100       22'        18.0"
100 E 19' 52.7"               100       19'        52.7"
101 W 43' 55.0"               101       43'        55.0"
101 E 43' 55.0"               101       43'        55.0"
96 N 45' 27.0"                96        45'        27.0"
1 143' W 15.0"                1         143'       15.0"

13 rows selected.
Previous Topic: How to check inserted records so far while executing a insert statement.
Next Topic: Check completion status from log table
Goto Forum:
  


Current Time: Sun Dec 04 08:43:48 CST 2016

Total time taken to generate the page: 0.07373 seconds