Home » SQL & PL/SQL » SQL & PL/SQL » Text to number conversion (Oracle SQL Developer)
Text to number conversion [message #679608] Tue, 10 March 2020 15:07 Go to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
Hi,

I would like to use a select statement to convert number stored as text to number.
Within case statement.
Case
if field text range is between '1' and '4000' then 'ROOM A'
if field text range is between '4001' and '10000' then 'ROOM B'

else 'unkown'
End location_room

To_number ?

Who can help me?

Theo


Re: Text to number conversion [message #679609 is a reply to message #679608] Tue, 10 March 2020 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Quote:
To_number ?

Yes.


Re: Text to number conversion [message #679610 is a reply to message #679608] Tue, 10 March 2020 15:19 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
Quote:
To_number ?
You may also need CASE
Re: Text to number conversion [message #679614 is a reply to message #679610] Tue, 10 March 2020 16:23 Go to previous messageGo to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
I do have a column with text values from 1 to 4000.
How do I convert these text values to numbers?
Can I use between statement?
Re: Text to number conversion [message #679615 is a reply to message #679614] Tue, 10 March 2020 16:31 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
dorustd wrote on Tue, 10 March 2020 16:23
I do have a column with text values from 1 to 4000.
How do I convert these text values to numbers?
You already asked if to_number would work, and Michael confirmed that it would. Have you tried it?

Quote:
Can I use between statement?
What happened when you tried it for yourself?
Re: Text to number conversion [message #679626 is a reply to message #679615] Wed, 11 March 2020 07:12 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
You would use regular expressions to check the value before converting the string to a number

CREATE TABLE CHECK_TABLE
(
  VALUE_CHAR  VARCHAR2(20)
);

Insert into CHECK_TABLE(VALUE_CHAR) Values('50A00');
Insert into CHECK_TABLE(VALUE_CHAR) Values('5000');
Insert into CHECK_TABLE(VALUE_CHAR) Values('ABCD23');
Insert into CHECK_TABLE(VALUE_CHAR) Values('12345');
Insert into CHECK_TABLE(VALUE_CHAR) Values('1');
COMMIT;

SELECT Value_char,
         CASE
             WHEN TO_NUMBER (Value_char) BETWEEN 1 AND 4000 THEN 'ROOM A'
             WHEN TO_NUMBER (Value_char) BETWEEN 4001 AND 10000 THEN 'ROOM B'
             ELSE 'UNKNOWN'
         END    Mapping
    FROM Check_table
   WHERE REGEXP_LIKE (Value_char, '^\d*$')
ORDER BY TO_NUMBER (Value_char);


VALUE_CHAR   MAPPING
1            ROOM A
5000         ROOM B
12345        UNKNOWN
Re: Text to number conversion [message #679633 is a reply to message #679626] Wed, 11 March 2020 13:38 Go to previous messageGo to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
Thank you...we are on the right track. I will explain my problem in more detail below.
I have a table with a column "position_code" which has the example following (text) values

position_code
- FA 9999 (all values begins with 'FA' are located in room 'ROOM A')
- FA 122 (all values begins with 'FA' are located in room 'ROOM A')
- FB 1234 (all values begins with 'FB' are located in room 'ROOM B')
- FB 12 (all values begins with 'FB' arelocated in room 'ROOM B')

(text) values between 1-4016 are located in room ROOM C
(text) values between 9896 -18633 are located in room ROOM D see below example position codes values
position_code
- 1 (value is located in room 'ROOM C')
- 106 (value is located in room 'ROOM C')
- 4016 (value is located in room 'ROOM C')
- 9896 (value is located in room 'ROOM D')
- 1111 (value is located in room 'ROOM D')
- 18633 (value is located in room 'ROOM D')

I have created statement below:
CASE
   WHEN substr(position_code,1,2) = 'FA'                 THEN 'ROOM A'
   WHEN substr(position_code,1,2) = 'FB'                 THEN 'ROOM B'
   WHEN to_number(position_code) between 1 and 4016      THEN 'ROOM C'
   WHEN to_number(position_code) between 9896 and 18633  THEN 'ROOM D'
  ELSE 'unknown'
END location

It works if position_code begins with FA and FB, but not for (text) numbers 1-4016 and 9896-18633

Can you help me?
Re: Text to number conversion [message #679634 is a reply to message #679633] Wed, 11 March 2020 14:03 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
SELECT position_code,
       CASE
           WHEN position_code LIKE 'FA%' THEN 'ROOM A'
           WHEN position_code LIKE 'FB%' THEN 'ROOM B'
           WHEN TO_NUMBER (position_code) BETWEEN 1 AND 4016 THEN 'ROOM C'
           WHEN TO_NUMBER (position_code) BETWEEN 9896 AND 18633 THEN 'ROOM D'
           ELSE 'unknown'
       END    Location
  FROM MY_TABLE_NAME
  WHERE REGEXP_LIKE(position_code,'^(?:FA|FB|\d*)\s*\d*$');
The regular expression means

^ start at the beginning of the line
(?:FA|FB|\d*) accept any of the following FA, FB or a numeric string of any length
\s* - Accdept white space of any length
\d* - accept any number of numericdigits
$ - end of line

Anything not in this pattern is ignored.

[Updated on: Wed, 11 March 2020 14:06]

Report message to a moderator

Re: Text to number conversion [message #679636 is a reply to message #679634] Wed, 11 March 2020 15:42 Go to previous messageGo to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
it works for FA and FB but not for to number values.
I get an error message:
ora-01722 invalid number
*cause
*error at line 1


and not all records are selected
Re: Text to number conversion [message #679639 is a reply to message #679636] Thu, 12 March 2020 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

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.

Use SQL*Plus and copy and paste your session, the WHOLE session.

[Updated on: Thu, 12 March 2020 01:06]

Report message to a moderator

Re: Text to number conversion [message #679650 is a reply to message #679639] Thu, 12 March 2020 05:57 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
Sorry I misunderstood your requirements. This will do what you want
SELECT Position,
       CASE
           WHEN Position LIKE 'FA%'
           THEN
               'ROOM A'
           WHEN Position LIKE 'FB%'
           THEN
               'ROOM B'
           WHEN REGEXP_LIKE (Position, '^\d*$')
           THEN
               CASE
                   WHEN TO_NUMBER (Position) BETWEEN 1 AND 4016
                   THEN
                       'ROOM C'
                   WHEN TO_NUMBER (Position) BETWEEN 9896 AND 18633
                   THEN
                       'ROOM D'
                   ELSE
                       'unknown'
               END
           ELSE
               'unknown'
       END    Location
  FROM My_table_name;
Re: Text to number conversion [message #679656 is a reply to message #679650] Thu, 12 March 2020 07:49 Go to previous messageGo to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
Yes, Yes.... this is working.
Selecting all records in query and number without the hyphen behind.

One thing I didn't mention regarding the position.code numbers.

Some position_code numbers do have hyphen with number behind. This gives 'Unknown'
When position_code is '103-3' BETWEEN 1 AND 4016 then 'ROOM C' Only need to check the number 103 before hyphen between 1 and 4016
When position_code is '10399-5' BETWEEN 9896 AND 18633 then 'ROOM D' Only need to check the number before hyphen between 9896 and 18633

Can I check only the number for the hyphen?
Re: Text to number conversion [message #679658 is a reply to message #679656] Thu, 12 March 2020 08:24 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
Please make sure you add all your requirements next time. Thanks

This query will return ROOM C for 103-13, but will return 'unknown' for 1234-ed because the hyphen isn't followed by another number. If you don't care what is after the hyphen change '^\d*-?\d*$' to '^\d*-?.*$'
SELECT Position,
       CASE
           WHEN Position LIKE 'FA%'
           THEN
               'ROOM A'
           WHEN Position LIKE 'FB%'
           THEN
               'ROOM B'
           WHEN REGEXP_LIKE (Position, '^\d*-?\d*$')
           THEN
               CASE
                   WHEN TO_NUMBER (REGEXP_SUBSTR (Position,
                                                  '^\d*',
                                                  1,
                                                  1)) BETWEEN 1
                                                          AND 4016
                   THEN
                       'ROOM C'
                   WHEN TO_NUMBER (REGEXP_SUBSTR (Position,
                                                  '^\d*',
                                                  1,
                                                  1)) BETWEEN 9896
                                                          AND 18633
                   THEN
                       'ROOM D'
                   ELSE
                       'unknown'
               END
           ELSE
               'unknown'
       END    Location
  FROM My_table_name
Re: Text to number conversion [message #679667 is a reply to message #679658] Thu, 12 March 2020 15:02 Go to previous messageGo to next message
dorustd
Messages: 6
Registered: March 2020
Junior Member
Great...it works well.
Yes... it's helped me a lot...thank you. I have to check 130000 position_codes / rows.

Thanks for you help!

Regards Theo
Re: Text to number conversion [message #679668 is a reply to message #679667] Thu, 12 March 2020 15:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I hope next time you'll follow the forum rules:

Michel Cadot wrote on Thu, 12 March 2020 07:05

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

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.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Re: Text to number conversion [message #680021 is a reply to message #679609] Sat, 18 April 2020 07:14 Go to previous message
Jamesconnor
Messages: 1
Registered: April 2020
Junior Member
Michel Cadot wrote on Tue, 10 March 2020 15:14



Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Quote:
To_number ?
Yes.


I have been searching for the same question. Thanks a lot.
Previous Topic: Function call in ref cursor
Next Topic: Hierarchy again and again
Goto Forum:
  


Current Time: Thu Jun 04 17:00:09 CDT 2020