I want to remove - sign value

-12345 I have a value like this in my colum some are 23678 i want to remove - sign value after removing - value should be like 12345

By using LTRIM function.u can remove - sign.

Hi Rohit,

ABS function can be used.
[B]ABS(n)[/B] function is used to retrieve the absolute value of the number.
Ex:
SQL> select abs(-123) from dual;

ABS(-123)
----------
123

ABS(argument) will work.

[quote=innila;9746]hi rohit, abs function can be used. [b]abs(n)[/b] function is used to retrieve the absolute value of the number. Ex: sql> select abs(-123) from dual; abs(-123) ---------- 123[/quote] hye thread i have a column varchar contaning values like this -12345 12345 65478 99999 -65432 i want to remove - sign i want that in this colum no "-" sign using substr or any other way

Rohit,

Please read the answers before asking the same questions.. your question has been answered ...

Thanks,
Suji

[quote=innila;9746]hi rohit, abs function can be used. [b]abs(n)[/b] function is used to retrieve the absolute value of the number. Ex: sql> select abs(-123) from dual; abs(-123) ---------- 123[/quote] hye innila i have a varchar field abs() not works with varchar i want to remove the minus sign from a colum supose colum name is item contaning value item -12345 12345 11111 11111
OUTPUT OF ITEM SHOULD BE 12345 12345 11111 11111 USING SUBSTR

[QUOTE=ROHIT_4444;9764]hye innila i have a varchar field abs() not works with varchar i want to remove the minus sign from a colum supose colum name is item

ITEM
-12345
-12345
-11111
11111 WANT TO REMOVE - SIGN USING SUBSTR

[quote=suji;9759]rohit, please read the answers before asking the same questions.. Your question has been answered ... Thanks, suji[/quote] it is answerd but wrong my field is varchar how abs() works i have a values like this colum name is item -12345,56789,34567,-23456 i want the output of colum item is like item 12345,56789,34567,23456 iam sorry to SAY YOU if you are unabel to give me the answer of the question then please mail me sorry we can't find it PLEASE SEE YOUR QUERY any other PLACE

Rohit,

You should use convert along with ABS to get your desired result...

The CONVERT function converts values from one type of data to another. CONVERT is primarily useful for changing values from a numeric or DATE data type to a text data type, or vice versa.

select ABS(convert(field1,PACKED)) as final result from table name

ABS function returns the absolute value of a number


convert(field1,PACKED) converts your varchar data to packed ... then ABS will return the absolute value of a number

Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest integer

Thanks,
Suji

[quote=suji;9759]rohit, please read the answers before asking the same questions.. Your question has been answered ... Thanks, suji[/quote] suji all answers are wrong ok, abs function not work with varchar field 1- select abs(convert(item,packed)) item from rr ora-00904: invalid column name 2-select abs(convert(item,packed)) as finalresult from rr ora-00904: invalid column name 3-select abs(convert(item,packed)) from rr ora-00904: invalid column name iam using function select replace(item,'-',' ' )item from rr it's working but give the output not save into the table i have crea new table every time like create <table name> as select replace(item,'-',' ' )item from rr i forget i use search or find command then replace the value and it saves into the table like rr ---------------------------------------- select item from rr if i use this command select replace(item,'-',' ')item from rr item ---------------------------------------- 8v165988000 8v165903100 8v165989000 8v165901400 8mws0800000 8msh08c2040 - 8v163721400 8m105499600 8q259500062 8m852101172 8v165750010 8v165903200 8v165903000 8m853490200 8v194511800 8v194511700 8v194511600 8v165900500 8v165900300 8v165900400 8m850911400 8g956010070 8v165220400 8v165220600 8m860243100 8m853408001 8mbh10c2100 8mws1000000 8mnh10c1000 8v163700200 8v163001500 8mbh10c2120 8mnn10c0000 8m850952001 8v167008400 8v165905500 8mng1000100 8v012622000 8msg08c0030 - 8v165290000 8d233327a00 8msr06c2033 8mws0600000 8v160002200 8t540000170 8t020107150 8t050300090 8t010102210 - 8v166050010 8v166601800 8msh08c2050 8mws0800000 8mwp0800000 8v166601900 8msh10c2050 8mwp1000000 8mws1000000 8t080801000 8v162330400 8msh12c2050 8mwp1200000 8mws1200000 8v166602100 8v1666020e0 8v166601300 8v166300310 8v166602200 8v166602300 8v1666026e0 8mnh10c1000 8v166602700 - 816a7003000 8v166660010 8v166950000 8v194603920 8v166602400 8v166602500 8v194606500 8mnh12c1000 8mbh12c2090 8v194603920 8v194603610 8v194606500 8mnh12c1000 8msh08c2040 8mbh12c2090 - 8v161750000 - 8v161752000 8v160003100 8v160003200 8f004716400 8v160004300 8v160004400 8v160004500 8f004731100 8p051151000 - 8v161754000 8pp10888000 8pp10889000 8g925577620 8v160003910 8v194309300 8v194309400 8v051717000 8t051300040 8v194310300 8t051300151 8t021300120 8pp11345000 8m005173500 8msh08c2053 8mwp0800003 8mnh08c1003 8m861711002 8pp11310000 8v194307500 8v193804010 8mws0800000 8v181702000 8m901715000 8msr05c2103 8mwp0500000 8mnh05c1003 - 8v161756000 8f176402600 8v160000910 8v160000810 8v194310000 8v160000700 8v160004100 8v194303000 8mws0500000 8mnh05c1003 8v124301900 8t021300080 8v194303500 8m851725000 8v160004810 8t111703700 8v124306400 8v160005400 8m980002070 8v191700600 8v160003800 8v181701400 8v160002000 - 8v161758000 8v161700400 8v194302900 8v194387500 8v194310400 8t104100200 8v194309500 8msh08c2053 8mws0800000 8msh12c2060 8mws1200000 8d813348000 8v194308100 8v194308000 8pp11000000 - 8v163566000 8v165901500 8v165901300 8v165901600 8v162704100 8v162704200 83623331500 8inh06f2000 - 8v162066000 8v160004200 8msh06c2040 8mwp0600000 8mws0600000 8mnh06c2000 - 816a4004a00 816f4003000 - 8v163200020 8v012622000 8v163200400 8v163200600 8v163370030 - 8v163310020 8v163310310 - 816f2004000 8pp11465000 8v163000210 8v163000420 8v163000820 8v163000910 8v163000620 8v163000320 8v163000720 8v165210800 8v163001200 816f4017000 816f4018000 816f4019000 816f4020000 8v163360010 8mbh10c2150 8mnn10c0000 8v163001400 8mng1000100 8msh10c2050 8mws1000000 8v166412500 8msh10c2070 8t010105050 8v166400310 8v166400400 8t011004850 8mnl24f0000 8v167001810 8v163532000 8mbh10c2170 8v166422500 - 816a6011a00 - 816a6016000 8v165900800 816hp006000 8ibh08c2100 8mws1200000 8t020108071 8v165900710 816hp007000 8v165905300 8m854539001 8v194512100 8v194513200 8m850946000 8v165480100 8g956010020 8m850998000 8v165902610 8v175400700 8v164201200 8pp10840000 8v184500600 8pp11293000 8ish06c2080 8v194510920 8v165901000 8mws1000000 8mwp1000003 8v193009000 - 8v165250000 8v165904800 8v165904700 8v165983000 8v165901300 8v165904600 8v165901400 8msh08c2050 8mws0800000 8ish08c2160 8mws1200000 8mwp1200000 816c6001000 - 8v165260000 8v165986000 8v165904900 8v165901600 8v165902000 8v165901900 8v165987000 8v165902300 8v165902100 8mws0800000 8mwp0800000 8msh08c2050 8v165720010 8v165730010 - 816a6018000 8v165100030 8v165900100 8mws1000000 8mws0800000 8v194511000 8v184500600 8v164201500 8isa06c0100 8mws1200000 8msh08c2050 8d910162000 8v164200800 8v164200700 8v164201700 8v164201600 816c6004000 8pp11745000 8msh10c2070 8t540000170 8d542181000 8v164201200 8v164201100 8v175400700 8v165740010 8t020107150 8v164200900 8v164201000 8msh12c2060 8t156707513 - 8v162313000 8v162301200 8v162301600 8v162301400 8n222812000 8mnh20f1003 8mws2000000 - 8v162058000 8v214030200 8v194513500 8v162301000 8v162300500 8v162300400 8v162300900 8msh10c2050 8mwp1000000 8mws1000000 8mnh10c1000 8m850911400 8t080600020 8v162330500 8v162320108 8msh08c2050 8v162320700 8mwp1000000 8msh16c2100 8mnn16c0000 8msh16c2090 8v166430000 8v162300300 8msh12c2060 8msh16c2110 8mnh16c1003 8mwp1600000 8v162320800 8v162320900 8mbh12c2240 8mwp1200000 8mnn12c0000 8v121100810 8v050801000 8v052805000 8v052804000 8v162330110 8v162330210 8v162330300 8g335212380 8v162301100 8v162320600 8v162320410 8v162300800 8v162320200 8v162320300 8mbh10c2120 8mnh10c1000 8v162321000 8msh10c2080 8v163801100 8v163801200 8mnh20c2000 8mnn20c2000 8v162310000 - 8v162060000 - 8v162062000 8v161802310 8d813786000 8pp11390000 8mws0500000 8msr05c2040 8e152901000 8mwp0600000 80ps1600300 8v161802100 8mnh08c2000 8v171800100 8v161802200 8msh06c2073 8mws0600000 8mwp0800000 8v192902200 8m893005000 - 8v162064000 8v162703500 8v162703400 8v162574000 8mws0800000 8mnh08c2000 8v162703600 8v162704000 8v162703710 8mwp1400000 80ps1600300 8v162703800 8msh08c2050 8mwp0800000 8v162703900 8v162705500 8v162703300 8v162576000 8v192500500 8v162701700 8v162700810 8v162701800 8t102500100 8v162701900 8v162702010 8d813369000 8v162702200 8d813353000 8d530016000 8m980002040 8msh16c2070 8mws1600000 8mwp1600000 8m980002070 8m980002080 8m980002090 8v162752010 8m980002060 8v162756010 8v162702400 8v162702500 8v162702600 8v162702300 8v192500600 8mnh10c2000 8v162701000 8mws1000000 8msh08c2060 8inh05f2000 8mwp1000000 8msh16c2100 8msh06c2060 8mws0600000 8v161803500 8t020108071 8msh08c2070 8mws0800000 8mnh08c1000 8v161803200 8msh08c2050 8mwp0800000 8v161803000 8v1618031a0 - 8v191401100 8v191401400 - 8v191401200 8v191401700 8v191401800 8v191401900 - 8v191401300 8v191401500 8v191401600 - 8v161900000 8v161802610 8v161802900 8v191600900 8v161803400 8m004161300 8v161803300 8v161801710 8v191600500 8v191600600 8v191600700 80ps1600300 8mwp1400000 8inl07c2000 - 8v161350000 8v165903900 8msh08c2060 8mws0800000 8msa08c0040 8mnh08c1003 - 8v162050000 816m2002000 8v162310400 8v184503000 8v162310200 8v162310100 8msh16c2080 8mws1600000 8v162310800 8t990102100 8v162200500 8v162310700 8t010102210 8m850911400 8t010705030 8v162300110 8v162310300 8t010700280 8t090723010 8v162310600 8v162300200 8t221020500 8t510974000 8t010704040 8t990401100 8t852300400 8t102200200 8t221006200 8t010700330 8t512320000 8t010700300 8t010705020 8t010705060 8t112200100 8t112200200 8t112200300 8t010705051 8t010705070 8t010507081 8t010705010 8t010704030 8v172400000 8t010700120 8mws0800000 8msh08c2040 8t220781000 8t852300300 8t010710010 8t512120000 8mbh12c2070 8m862303000 8t990400600 8t080700520 8t010700340 8t010700130 8t102480500 8t991700100 8t010700110 8t513027000 8t010700150 8t220787500 8mws1000000 8t010700010 8t080300301 8t080300290 8t010700020 8mws1200000 8m852411000 8t010700140 8t220781500 8t122400500 8t102480100 8t010700180 8m852402002 8msh06c2030 8mws0600000 8t512944000 8msh10c2070 8t990400200 8t990400100 8t010901120 8t010901140 8t990400400 8t157727000 8t990400300 8t991680100 8t010901110 8t512917000 8t513022000 8mng1000100 8mng0600100 8t010111060 8t800046700 8t080700570 8t540000180 8t540000170 8t512240000 8t156745013 8t010700160 8t102300300 8t513400030 8t090722060 8t080700040 8t102300400 8t102300500 8m850043000 8t010700220 8t157721700 8mwp2000000 8mws1000000 8v161801000 8m004110700 - 8v160240010 8v191900800 8v051402000 - 816a5001000 8v164100010 8v164100200 8v164100500 8v160070020 8m850143000 8m850144000 8mnh36m2000 80ps6300750 8mng1000100 8v164203500 8pp11054000 8v164203600 8pp11053000 8v164203700 8msh08c2050 - 8v164270020 - 8m852601012 8q259500070 8q259500070 8v164270110 8v164270220 - 8v164272020 - 8m852601012 8q259500070 8q259500070 8v164270110 8v164272120 8m852605001 8m852606001 8n105562900 8m852608001 8m852609002 8n221041600 8m852610000 8m852611001 8m852612000 8m852607000 8m980001990 8m980002000 8isa06c0040 8m853901000 8m903203001 8m852603000 8n221214400 8m852618000 8m852604001 8v164201810 8v164200500 80ps3000500 8msh12c2060 8mws1200000 8mwp1200000 8v164201900 8v164202000 8v163900100 8t552502000 8t553002500 8m105531800 8v052628000 8msh12c2050 8v164203000 8v164203300 - 8v166850000 - 8v166852000 8v160006300 8msh06c2040 8mws0600000 8mwp0600000 8mnh06c1000 8m105413500 8m105417500 8n155406000 8m105412800 842c6012000 8e153780200 8v184602010 8m105417600 8v160000520 8m105413000 8v181701500 8v181701600 8m105412900 8v194306900 8v194306800 8v160003300 8m105413100 8v182604310 8msh10c2050 8mws1000000 8mnh10c1000 - 8v166856000 8n250508500 8n251002500 8n251018000 8n251057600 8n251057400 8n251061800 8n380545100 8n380564000 8n380575000 8n380912300 8n381106000 8n381524000 8n251053500 816a9007000 - 8v160200000 8v160210000 - 816a1008000 8v161673010 8msh10c2070 8mwp1000000 8mws1000000 8mnh10c1000 8v161600700 816c1014a00 - 8v160230000 8t050300090 8t010102210 8t080604001 8v160002200 8v194505700 8t540000170 8t020107150 8v191100210 8msh10c2050 717 rows selected 8v165988000 8v165903100 8v165989000 8v165901400 8mws0800000 8msh08c2040 8v163721400 8m105499600 8q259500062 8m852101172 8v165750010 8v165903200 8v165903000 8m853490200 8v194511800 8v194511700 8v194511600 8v165900500 8v165900300 8v165900400 8m850911400 8g956010070 8v165220400 8v165220600 8m860243100 8m853408001 8mbh10c2100 8mws1000000 8mnh10c1000 8v163700200 8v163001500 8mbh10c2120 8mnn10c0000 8m850952001 8v167008400 8v165905500 8mng1000100 8v012622000 8msg08c0030 8v165290000 8d233327a00 8msr06c2033 8mws0600000 8v160002200 8t540000170 8t020107150 8t050300090 8t010102210 8v166050010 8v166601800 8msh08c2050 8mws0800000 8mwp0800000 8v166601900 8msh10c2050 8mwp1000000 8mws1000000 8t080801000 8v162330400 8msh12c2050 8mwp1200000 8mws1200000 8v166602100 8v1666020e0 8v166601300 8v166300310 8v166602200 8v166602300 8v1666026e0 8mnh10c1000 8v166602700 816a7003000 8v166660010 8v166950000 8v194603920 8v166602400 8v166602500 8v194606500 8mnh12c1000 8mbh12c2090 8v194603920 8v194603610 8v194606500 8mnh12c1000 8msh08c2040 8mbh12c2090 8v161750000 8v161752000 8v160003100 8v160003200 8f004716400 8v160004300 8v160004400 8v160004500 8f004731100 8p051151000 8v161754000 8pp10888000 8pp10889000 8g925577620 8v160003910 8v194309300 8v194309400 8v051717000 8t051300040 8v194310300 8t051300151 8t021300120 8pp11345000 8m005173500 8msh08c2053 8mwp0800003 8mnh08c1003 8m861711002 8pp11310000 8v194307500 8v193804010 8mws0800000 8v181702000 8m901715000 8msr05c2103 8mwp0500000 8mnh05c1003 8v161756000 8f176402600 8v160000910 8v160000810 8v194310000 8v160000700 8v160004100 8v194303000 8mws0500000 8mnh05c1003 8v124301900 8t021300080 8v194303500 8m851725000 8v160004810 8t111703700 8v124306400 8v160005400 8m980002070 8v191700600 8v160003800 8v181701400 8v160002000 8v161758000 8v161700400 8v194302900 8v194387500 8v194310400 8t104100200 8v194309500 8msh08c2053 8mws0800000 8msh12c2060 8mws1200000 8d813348000 8v194308100 8v194308000 8pp11000000 8v163566000 8v165901500 8v165901300 8v165901600 8v162704100 8v162704200 83623331500 8inh06f2000 8v162066000 8v160004200 8msh06c2040 8mwp0600000 8mws0600000 8mnh06c2000 816a4004a00 816f4003000 8v163200020 8v012622000 8v163200400 8v163200600 8v163370030 8v163310020 8v163310310 816f2004000 8pp11465000 8v163000210 8v163000420 8v163000820 8v163000910 8v163000620 8v163000320 8v163000720 8v165210800 8v163001200 816f4017000 816f4018000 816f4019000 816f4020000 8v163360010 8mbh10c2150 8mnn10c0000 8v163001400 8mng1000100 8msh10c2050 8mws1000000 8v166412500 8msh10c2070 8t010105050 8v166400310 8v166400400 8t011004850 8mnl24f0000 8v167001810 8v163532000 8mbh10c2170 8v166422500 816a6011a00 816a6016000 8v165900800 816hp006000 8ibh08c2100 8mws1200000 8t020108071 8v165900710 816hp007000 8v165905300 8m854539001 8v194512100 8v194513200 8m850946000 8v165480100 8g956010020 8m850998000 8v165902610 8v175400700 8v164201200 8pp10840000 8v184500600 8pp11293000 8ish06c2080 8v194510920 8v165901000 8mws1000000 8mwp1000003 8v193009000 8v165250000 8v165904800 8v165904700 8v165983000 8v165901300 8v165904600 8v165901400 8msh08c2050 8mws0800000 8ish08c2160 8mws1200000 8mwp1200000 816c6001000 8v165260000 8v165986000 8v165904900 8v165901600 8v165902000 8v165901900 8v165987000 8v165902300 8v165902100 8mws0800000 8mwp0800000 8msh08c2050 8v165720010 8v165730010 816a6018000 8v165100030 8v165900100 8mws1000000 8mws0800000 8v194511000 8v184500600 8v164201500 8isa06c0100 8mws1200000 8msh08c2050 8d910162000 8v164200800 8v164200700 8v164201700 8v164201600 816c6004000 8pp11745000 8msh10c2070 8t540000170 8d542181000 8v164201200 8v164201100 8v175400700 8v165740010 8t020107150 8v164200900 8v164201000 8msh12c2060 8t156707513 8v162313000 8v162301200 8v162301600 8v162301400 8n222812000 8mnh20f1003 8mws2000000 8v162058000 8v214030200 8v194513500 8v162301000 8v162300500 8v162300400 8v162300900 8msh10c2050 8mwp1000000 8mws1000000 8mnh10c1000 8m850911400 8t080600020 8v162330500 8v162320108 8msh08c2050 8v162320700 8mwp1000000 8msh16c2100 8mnn16c0000 8msh16c2090 8v166430000 8v162300300 8msh12c2060 8msh16c2110 8mnh16c1003 8mwp1600000 8v162320800 8v162320900 8mbh12c2240 8mwp1200000 8mnn12c0000 8v121100810 8v050801000 8v052805000 8v052804000 8v162330110 8v162330210 8v162330300 8g335212380 8v162301100 8v162320600 8v162320410 8v162300800 8v162320200 8v162320300 8mbh10c2120 8mnh10c1000 8v162321000 8msh10c2080 8v163801100 8v163801200 8mnh20c2000 8mnn20c2000 8v162310000 8v162060000 8v162062000 8v161802310 8d813786000 8pp11390000 8mws0500000 8msr05c2040 8e152901000 8mwp0600000 80ps1600300 8v161802100 8mnh08c2000 8v171800100 8v161802200 8msh06c2073 8mws0600000 8mwp0800000 8v192902200 8m893005000 8v162064000 8v162703500 8v162703400 8v162574000 8mws0800000 8mnh08c2000 8v162703600 8v162704000 8v162703710 8mwp1400000 80ps1600300 8v162703800 8msh08c2050 8mwp0800000 8v162703900 8v162705500 8v162703300 8v162576000 8v192500500 8v162701700 8v162700810 8v162701800 8t102500100 8v162701900 8v162702010 8d813369000 8v162702200 8d813353000 8d530016000 8m980002040 8msh16c2070 8mws1600000 8mwp1600000 8m980002070 8m980002080 8m980002090 8v162752010 8m980002060 8v162756010 8v162702400 8v162702500 8v162702600 8v162702300 8v192500600 8mnh10c2000 8v162701000 8mws1000000 8msh08c2060 8inh05f2000 8mwp1000000 8msh16c2100 8msh06c2060 8mws0600000 8v161803500 8t020108071 8msh08c2070 8mws0800000 8mnh08c1000 8v161803200 8msh08c2050 8mwp0800000 8v161803000 8v1618031a0 8v191401100 8v191401400 8v191401200 8v191401700 8v191401800 8v191401900 8v191401300 8v191401500 8v191401600 8v161900000 8v161802610 8v161802900 8v191600900 8v161803400 8m004161300 8v161803300 8v161801710 8v191600500 8v191600600 8v191600700 80ps1600300 8mwp1400000 8inl07c2000 8v161350000 8v165903900 8msh08c2060 8mws0800000 8msa08c0040 8mnh08c1003 8v162050000 816m2002000 8v162310400 8v184503000 8v162310200 8v162310100 8msh16c2080 8mws1600000 8v162310800 8t990102100 8v162200500 8v162310700 8t010102210 8m850911400 8t010705030 8v162300110 8v162310300 8t010700280 8t090723010 8v162310600 8v162300200 8t221020500 8t510974000 8t010704040 8t990401100 8t852300400 8t102200200 8t221006200 8t010700330 8t512320000 8t010700300 8t010705020 8t010705060 8t112200100 8t112200200 8t112200300 8t010705051 8t010705070 8t010507081 8t010705010 8t010704030 8v172400000 8t010700120 8mws0800000 8msh08c2040 8t220781000 8t852300300 8t010710010 8t512120000 8mbh12c2070 8m862303000 8t990400600 8t080700520 8t010700340 8t010700130 8t102480500 8t991700100 8t010700110 8t513027000 8t010700150 8t220787500 8mws1000000 8t010700010 8t080300301 8t080300290 8t010700020 8mws1200000 8m852411000 8t010700140 8t220781500 8t122400500 8t102480100 8t010700180 8m852402002 8msh06c2030 8mws0600000 8t512944000 8msh10c2070 8t990400200 8t990400100 8t010901120 8t010901140 8t990400400 8t157727000 8t990400300 8t991680100 8t010901110 8t512917000 8t513022000 8mng1000100 8mng0600100 8t010111060 8t800046700 8t080700570 8t540000180 8t540000170 8t512240000 8t156745013 8t010700160 8t102300300 8t513400030 8t090722060 8t080700040 8t102300400 8t102300500 8m850043000 8t010700220 8t157721700 8mwp2000000 8mws1000000 8v161801000 8m004110700 8v160240010 8v191900800 8v051402000 816a5001000 8v164100010 8v164100200 8v164100500 8v160070020 8m850143000 8m850144000 8mnh36m2000 80ps6300750 8mng1000100 8v164203500 8pp11054000 8v164203600 8pp11053000 8v164203700 8msh08c2050 8v164270020 8m852601012 8q259500070 8q259500070 8v164270110 8v164270220 8v164272020 8m852601012 8q259500070 8q259500070 8v164270110 8v164272120 8m852605001 8m852606001 8n105562900 8m852608001 8m852609002 8n221041600 8m852610000 8m852611001 8m852612000 8m852607000 8m980001990 8m980002000 8isa06c0040 8m853901000 8m903203001 8m852603000 8n221214400 8m852618000 8m852604001 8v164201810 8v164200500 80ps3000500 8msh12c2060 8mws1200000 8mwp1200000 8v164201900 8v164202000 8v163900100 8t552502000 8t553002500 8m105531800 8v052628000 8msh12c2050 8v164203000 8v164203300 8v166850000 8v166852000 8v160006300 8msh06c2040 8mws0600000 8mwp0600000 8mnh06c1000 8m105413500 8m105417500 8n155406000 8m105412800 842c6012000 8e153780200 8v184602010 8m105417600 8v160000520 8m105413000 8v181701500 8v181701600 8m105412900 8v194306900 8v194306800 8v160003300 8m105413100 8v182604310 8msh10c2050 8mws1000000 8mnh10c1000 8v166856000 8n250508500 8n251002500 8n251018000 8n251057600 8n251057400 8n251061800 8n380545100 8n380564000 8n380575000 8n380912300 8n381106000 8n381524000 8n251053500 816a9007000 8v160200000 8v160210000 816a1008000 8v161673010 8msh10c2070 8mwp1000000 8mws1000000 8mnh10c1000 8v161600700 816c1014a00 8v160230000 8t050300090 8t010102210 8t080604001 8v160002200 8v194505700 8t540000170 8t020107150 8v191100210 8msh10c2050 717 rows selected

Hi Rohit,

Since u arent able to use abs function in a varchar column, then try converting the varchar into number & then identify the absolute value,

[QUOTE]

SQL> select eno, abs(to_number(eno)) from emp1;

ENO ABS(TO_NUMBER(ENO))
----- -------------------
-123 123

[/QUOTE]

The column ENO has a varchar2 datatype (im unable to create varchar datatype due to some restrictions), which has a value '-123', after converting the abs function is used to retrieve the absolute value 123.

Hopefully its clear & works for u.

[quote=suji;9759]rohit, please read the answers before asking the same questions.. Your question has been answered ... Thanks, suji[/quote] dear suji first of all i don't asked same question in my second question i describe my field is varchar-------------------------------------- 1- select abs(convert(item,packed)) item from rr ora-00904: invalid column name---------------------------- 2-select abs(convert(item,packed)) as finalresult from rr ora-00904: invalid column name-------------------------- 3-select abs(convert(item,packed)) from rr ora-00904: invalid column name ----------------------------- iam using function select replace(item,'-',' ' )item from rr it's working but give the output not save into the table i have crea new table every time like ---------------------------------- [b] create <table name> as select replace(item,'-',' ' )item from rr [/b] [b]i forget i use search or find command then replace the value and it saves into the table like rr[/b] thanks rohit

[quote=innila;9873]hi rohit, since u arent able to use abs function in a varchar column, then try converting the varchar into number & then identify the absolute value, the column eno has a varchar2 datatype (im unable to create varchar datatype due to some restrictions), which has a value '-123', after converting the abs function is used to retrieve the absolute value 123. Hopefully its clear & works for u.[/quote] thanks but it's not working ---------select item, abs(to_number(item)) from rr1 ora-01722: invalid number ------- iam using function select replace(item,'-',' ' )item from rr ------------------- it's working but give the output not save into the table i have crea new table every time like -------

[B]create [I]table name[/I] as [I]select replace(item,'-',' ' )item from rr[/I] i forget i use search or find command then replace the value and it saves into the table like rr[/B]

Hi Rohit,

Cant u update the row instead of creating a new table?
What is the Oracle version your using?

[quote=innila;9882]hi rohit, cant u update the row instead of creating a new table?
what is the oracle version your using?[/quote] thanks thread dear thread, create table name as [b]select replace(item,'-',' ' )item from rr [/b]
i forget i use search or find command then replace the value and it saves into the table like rr [b]iam using oracle 9i i update my table through this function and it works [/b]
[b]update rr set item=replace(item,'-',' ')[/b] but i dont want to update also please help me thanks rohit