Question 113614 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL READING THE COLUMN VALUES?

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.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0 
    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;

  2. 0

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

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search