SQL READING THE COLUMN VALUES?

suprith M 0
Viewed 55

0.0.0.1 saved in sql table column as 10001. MY data base contains values as such above mentioned i wanted to sort it based on the values but if I sort is as it is it will give me wrong order so i need tp convert it to the above mentioned format(10001). i.e. Remove the dots(.)

Thank you.

oracle-sqldeveloper oracle mysql sql
4 Answers

(I guess you're actually using Oracle as a database, regarding the tool - Oracle SQL Developer - you've also tagged, which means that MySQL tag should be removed).

To me, it looks as if you'd want to a) remove dots, b) change datatype to number (so that it is correctly sorted):

order by to_number(replace(col, '.', ''))

It presumes that only characters allowed are digits and dots. If there's a value like 'A.0.0.1', it'll - of course - fail, as you can't convert letter A to a number.

Littlefoot0

Why are you storing the period '.' Character to begin with? If it's not needed you can remove it.

If you want to remove all non-alphanumeric characters you could use a regular expresion.


create table t (nm varchar2(20));

insert into t values ('.0.0.0.1');
insert into t values ('10.0.1.1.0');
commit;
select * from t;

NM
.0.0.0.1
10.0.1.1.0


update t 
set    nm = regexp_replace(
         regexp_replace(nm, '[^A-Z0-9 ]', ''),
         ' {2,}', ' '
       );

select * from t;

NM
0001
100110
Beefstu0

You can use the translate command with a SELECT and the data will not be charged in the table.

See below

create table t (nm varchar2(20));

insert into t values ('.0.0.0.1');
insert into t values ('10.0.1.1.0');
commit;


SELECT translate(nm, '*.','*') from t

TRANSLATE(NM,'*.','*')
0001
100110
Beefstu0 
SELECT DISTINCT(column_name)
FROM Table_name
ORDER BY 
TO_NUMBER (REGEXP_SUBSTR (column_name, '\d+',1,2)),
TO_NUMBER (REGEXP_SUBSTR (column_name,'\d+',1,3)) NULLS FIRST,
TO_NUMBER (REGEXP_SUBSTR (column_name,'\d+',1,4)) NULLS FIRST;
suprith M0
Related