Text to number conversion [message #679608] |
Tue, 10 March 2020 15:07 |
|
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 #679626 is a reply to message #679615] |
Wed, 11 March 2020 07:12 |
Bill B
Messages: 1971 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 #679634 is a reply to message #679633] |
Wed, 11 March 2020 14:03 |
Bill B
Messages: 1971 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 #679650 is a reply to message #679639] |
Thu, 12 March 2020 05:57 |
Bill B
Messages: 1971 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 #679658 is a reply to message #679656] |
Thu, 12 March 2020 08:24 |
Bill B
Messages: 1971 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 #680021 is a reply to message #679609] |
Sat, 18 April 2020 07:14 |
|
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.
|
|
|