Tags: col, column, database, function, isnumeric, mysql, oracle, serverthis, sql, table, tbl
Thread: isnumeric
4,763 words with 11 Comments; publish: Sat, 09 Feb 2008 08:20:00 GMT; (25062.50, « »)
Is there a function in ORACLE like the isnumeric in SQL Server?
This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.
In SQL Server, This is how I will do it
SELECT * FROM TBL
COL
--
1
11
1123
ABC
ZZ
18AB
SELECT CASE ISNUMERIC(COL)
WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
ELSE COL
END AS COL
FROM TBL
COL
--
00001
00011
01123
ABC
ZZ
18AB
Thanks
http://oracle.itags.org/q_oracle_256317.html
All Comments
Leave a comment...
- 11 Comments
select col, decode(replace(translate(col, '1234567890.', '00000000000'), '0', null), null, lpad(col, 5, '0'), col) col from tbl;
COL COLPA-- --1 0000111 000111123 01123ABC ABCZZ ZZ18AB 18AB12345 12345
#1; Sat, 23 Feb 2008 21:50:00 GMT
- Look This:
Tbl (
coll varchar2(05))
begin
declare
cursor c1 is
select coll
from tbl;
value_n number(10);
begin
for r1 in c1 loop
begin
value_n := to_number(r1.coll);
update tbl
set coll = lpad(r1.coll,5,'0')
where coll = r1.coll;
exception
when others then
null;
end;
end loop;
end;
commit;
end;
Good Luck
#2; Sat, 23 Feb 2008 21:51:00 GMT
- CREATE OR REPLACE FUNCTION isnumber( p_in_data IN VARCHAR2 ) RETURN BOOLEAN
v_temp NUMBER;
BEGIN
v_temp := TO_NUMBER( p_in_data );
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
/
#3; Sat, 23 Feb 2008 21:52:00 GMT
- I forgot to put the select in my reply above.
SELECT decode(ISNUMERIC(COL), TRUE, RIGHT('00000'+LTRIM(RTRIM(COL)),5), COL ) AS COL
FROM TBL
Lewis
#4; Sat, 23 Feb 2008 21:53:00 GMT
- I also spelled my function isnumber not isnumeric. It's been a long day.
>I forgot to put the select in my reply above.
>SELECT decode(ISNUMERIC(COL), TRUE, RIGHT('00000'+LTRIM(RTRIM(COL)),5), COL ) AS COL
>FROM TBL
>Lewis
#5; Sat, 23 Feb 2008 21:54:00 GMT
- Not to say the other solution will work this may be simpler to implement
Using you example table
select
decode(decode(UPPER(col), LOWER(col), 1, 0),
1, Lpad(col,5,'0'), col) col
from test_num
RESULTS
COL
--
00001
00011
01123
ABC
ZZ
18AB
>Is there a function in ORACLE like the isnumeric in SQL Server?
>This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.
>In SQL Server, This is how I will do it
>SELECT * FROM TBL
>COL
>--
>1
>11
>1123
>ABC
>ZZ
>18AB
>SELECT CASE ISNUMERIC(COL)
> WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
> ELSE COL
> END AS COL
>FROM TBL
>COL
>--
>00001
>00011
>01123
>ABC
>ZZ
>18AB
>Thanks
#6; Sat, 23 Feb 2008 21:55:00 GMT
- Not to say the other solution will not work this may be simpler to implement
Using you example table
select
decode(decode(UPPER(col), LOWER(col), 1, 0),
1, Lpad(col,5,'0'), col) col
from test_num
RESULTS
COL
--
00001
00011
01123
ABC
ZZ
18AB
>Is there a function in ORACLE like the isnumeric in SQL Server?
>This is what I would like to do: I have a table tbl that has one column col varchar2(5) I would like to append 0 to the begining of the column if the field is numeric.
>In SQL Server, This is how I will do it
>SELECT * FROM TBL
>COL
>--
>1
>11
>1123
>ABC
>ZZ
>18AB
>SELECT CASE ISNUMERIC(COL)
> WHEN 1 THEN RIGHT('00000'+LTRIM(RTRIM(COL)),5)
> ELSE COL
> END AS COL
>FROM TBL
>COL
>--
>00001
>00011
>01123
>ABC
>ZZ
>18AB
>Thanks
#7; Sat, 23 Feb 2008 21:56:00 GMT
- Here's another way.
Similar to one of Lewis Cunningham's posts above.
This function will return a 1 if the string is a number, and a 0 if it is not.
CREATE OR REPLACE FUNCTION is_number(string_in VARCHAR2)RETURN NUMBERAS BEGIN RETURN SIGN(ABS(TO_NUMBER(string_in))+1);
EXCEPTION WHEN VALUE_ERROR THEN RETURN 0;
END;
/ SELECT IS_NUMBER(12345) is_numFROM dual IS_NUM -- 1 SELECT IS_NUMBER(-123.66) is_numFROM dual IS_NUM -- 1 SELECT IS_NUMBER('XXX123') is_numFROM dual IS_NUM -- 0 SELECT IS_NUMBER('.oracle.itags.org.#$%^') is_numFROM dual IS_NUM -- 0
#8; Sat, 23 Feb 2008 21:57:00 GMT
- Todd's approach is the BEST in this regard.
Thanks,
Sri DHAR
#9; Sat, 23 Feb 2008 21:58:00 GMT
- http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2460648511591#10; Sat, 23 Feb 2008 21:59:00 GMT
- Thanks.
This worked!!
#11; Sat, 23 Feb 2008 22:00:00 GMT