SQL READING THE COLUMN VALUES?-CodePudding

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.

CodePudding user response：

(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.

CodePudding user response：

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