Identifying Special Characters in a Column

[uparna]

Hi ,
I have a varchar2 column in a table of mine. Here is an example :

Tablename : Flowers

ID------NAME-----------------PRICE
1-------Chrysanthemum-----------1.09
2-------Catstail---------------------1.32
3-------RedRoses-------------------6.12
4-------Pinkie's Roses--------------3.67
5-------Orchid # 451---------------5.01
6-------Dahliâ-----------------------0.90

<ignore the '-'s they are there to preserve some sort of formatting in the post thats all>

Now there are 1000s of records and i have to write a query that returns to me the records which have a 'special character' in the 'NAME' column. For eg , any name that has a character other than abcd..z (both upper and lower cases)

In the above example , Pinkie's roses (for a space and aphostophy) , Orchids (for the # and 4 5 and 1) , and dahliâ (for the â) should be returned by the query. The names are of varying lengths, and have a maximum length of 100. There are instances where the name starts with a special char too.

Any ideas for me? Thanks !

Regards,
S. Jayaram Uparna .

 

[sem]

Use TRANSLATE function:

...
where TRANSLATE(LOWER(NAME), ' abcdefghijklmnopqrstuvwxyz',' ') is not NULL

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Jayaram,

Please correct me if I am wrong, but do you not consider a blank character to be "non-special". If that is the case, then I believe Sem/Dima's code needs a slight adjustment (Since 'Sweet Pea' would result in a special-character result if we do not change the code to the following):

...
where trim(TRANSLATE(LOWER(NAME), ' abcdefghijklmnopqrstuvwxyz',' ')) is not NULL

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:37 (29Jun04) UTC (aka "GMT" and "Zulu"), 09:37 (29Jun04) Mountain Time)

 

[SantaMufasa]

Also, Jayaram, if you want some content of a Tek-Tips posting to align properly without using some "alignment characters" as you did in your original post, you can turn alignment on and off with "{code}" and "{/code}", but use square brackets ("[ ]") instead of curly braces ("{ }").

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:42 (29Jun04) UTC (aka "GMT" and "Zulu"), 09:42 (29Jun04) Mountain Time)

 

[uparna]

Sem , Santa ,
Excellent. I realise my folly more and more nowadays....the answer stares at my face and i simply dont see it. Both your answers are right. I have two difft requirements where i need space to be considered as a special char in one and an accepted char in another, so i will use both these queries....heh heh heh :-D .. Thanks guys , you are the BEST !

Regards,
S. Jayaram Uparna .