OraFAQ Forum: SQL & PL/SQL » Select sql with junk character data.

Home » SQL & PL/SQL » SQL & PL/SQL » Select sql with junk character data.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select sql with junk character data. [message #147335]

Thu, 17 November 2005 04:40

purveshvora
Messages: 6
Registered: August 2005
Location: Mumbai

Junior Member

Hi,

I have a simple select query to get few rows from a table.

Now the data in Oracle has some junk characters like tab newline etc. I want to get ONLY AlphaNumeric data for that column.

Is there anyway to achieve this?

Thanks & Reagards,
Purvesh

Report message to a moderator

Re: Select sql with junk character data. [message #147349 is a reply to message #147335]

Thu, 17 November 2005 05:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Replace can help you out.

SELECT REPLACE( REPLACE( thecolumn
                       , CHR(9)
                       , ''
                       )
              , CHR(10)
              , ''
              ) thecolumn
  FROM yourtable

MHE

Report message to a moderator

Re: Select sql with junk character data. [message #147366 is a reply to message #147349]

Thu, 17 November 2005 07:02

purveshvora
Messages: 6
Registered: August 2005
Location: Mumbai

Junior Member

Maaher,

Thanks for your reply. It works and removes tabs and new line characters.

But I want to select Only Alpha-Numeric Characters from the data. If I add all the special characters and non-alphanumeric characters in the sql then it will be very long.

Is there a better way to do this.

Thanks & Reagards,
Purvesh

Report message to a moderator

Re: Select sql with junk character data. [message #147370 is a reply to message #147366]

Thu, 17 November 2005 07:21

Maaher
Messages: 7065
Registered: December 2001

Senior Member

In fact there is, but it involves creating your own (deterministic) function.

If I find the time, I'll try to create an example.

MHE

Report message to a moderator

Re: Select sql with junk character data. [message #147464 is a reply to message #147349]

Thu, 17 November 2005 13:18

cmerry
Messages: 109
Registered: November 2005
Location: Idaho

Senior Member

Or rather than nesting REPLACE, use TRANSLATE.

SQL> SELECT 'This string ' || CHR(10) || 'has a lot' || CHR(9) || ' of whitespace' string
  2  FROM dual;

STRING
-------------------------------------
This string
has a lot        of whitespace

SQL> SELECT TRANSLATE('This string ' || CHR(10) || 'has a lot' || CHR(9) || ' of whitespace'
  2                  ,'A'||CHR(10)||CHR(9)
  3                  ,'A') string
  4  FROM dual;

STRING
-----------------------------------
This string has a lot of whitespace

The 'A' character is simply a placeholder for the 3rd parameter because it cannot be NULL.

Report message to a moderator

Re: Select sql with junk character data. [message #147496 is a reply to message #147349]

Thu, 17 November 2005 20:46

cmerry
Messages: 109
Registered: November 2005
Location: Idaho

Senior Member

Oooohhh, you mean something like this...

SQL> SELECT 'ABC$DEF(HIJ#KLM*NOP.QRS?TUV!WXY@Z' str
  2  FROM dual;

STR
---------------------------------
ABC$DEF(HIJ#KLM*NOP.QRS?TUV!WXY@Z

SQL> SELECT REGEXP_REPLACE('ABC$DEF(HIJ#KLM*NOP.QRS?TUV!WXY@Z', '[^[:alnum:]]', NULL) str
  2  FROM dual;

STR
-------------------------
ABCDEFHIJKLMNOPQRSTUVWXYZ

Regular expressions are only available in 10g, however!!!

Report message to a moderator