| Search for AlphaNumeric Characters in a column value [message #8138] |
Tue, 29 July 2003 18:33  |
Vinod
Messages: 76
Registered: April 1999
|
Member |
|
|
Hi
I would like to spool out all rows from a table that has alphanumeric characters anywhere in one of its column.
Example:
TableName : Contact
Column Names: ID, FirstName, Lastname
Query : Get all records, where the FirstName
column has any alphanumeric character
such as * or % or $ or @ etc.
Records:
ID FName LNAME
10, Vinod, Srinivasan
20, V%inod, Testme
30, Srin*, Testall
Result should be:
20, V%inod, Testme
30, Srin*, Testall
Please advice. I need this in SQL script. I can get this in PL-SQL but I would like to know if SQL is possible.
thanks
Vinod
|
|
|
|
| Re: Search for AlphaNumeric Characters in a column value [message #8141 is a reply to message #8138] |
Tue, 29 July 2003 21:06  |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
You can try this out :-
1) User Upper function to convert the string to Upper
case
2) Use Translate function to convert each character to
'0' which is not part of the search string
'1' - Alphanumeric Character to search in the string
3) Search the result string for value '1'
Here's the code :-
Test Db>select * from tmp;
COL1
----------------------------------------------------------------------------------------------------
20, V%inod, Testme
10, Vinod, Srinivasan
30, Srin*, Testall
Test Db>select a.col1
2 from tmp a
3 Where Instr(Translate(Upper(a.Col1),'()!-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,@#$%*','000000000
0000000000000000000000000000000011111'),'1') > 0
4 /
COL1
----------------------------------------------------------------------------------------------------
20, V%inod, Testme
30, Srin*, Testall
Test Db>
|
|
|
|
|
|
|
|
|
|
| Re: Search for AlphaNumeric Characters in a column value [message #8158 is a reply to message #8143] |
Wed, 30 July 2003 18:50 |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Todd, I guess the solution you have posted in "EXPERT" forum does not work.
Test Db>select *
2 from tmp
3 where replace(translate(lower(col1),'abcdefghijklmnopqrstuvwxyz',rpad('a', 26, 'a')),'a')
4 is not null
5 /
COL1
-------------------------------------------------------------------------------------------------
20, V%inod, Testme
10, Vinod, Srinivasan
30, Srin*, Testall
I don't think this is the output what is expected.
If you have time do have a look again at the requirement posted.
|
|
|
|
| Re: Search for AlphaNumeric Characters in a column value [message #8160 is a reply to message #8158] |
Wed, 30 July 2003 18:59 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Those are supposed to be three separate columns - you just threw everything, including the commas and numbers, into one column. Each column has to be evaluated on its own.
sql>select * from rc;
ID FNAME LNAME
--------- ------------------------------ ------------------------------
10 Vinod Srinivasan
20 V%inod Testme
30 Srin* Testall
3 rows selected.
sql>select *
2 from rc
3 where replace(
4 translate(lower(fname), 'abcdefghijklmnopqrstuvwxyz', rpad('a', 26, 'a')),
5 'a') is not null;
ID FNAME LNAME
--------- ------------------------------ ------------------------------
20 V%inod Testme
30 Srin* Testall
2 rows selected.
If you want to check across all columns, then the correct way to do this is (note I have added one more row that has a "bad" last name):
sql>select * from rc;
ID FNAME LNAME
--------- ------------------------------ ------------------------------
10 Vinod Srinivasan
20 V%inod Testme
30 Srin* Testall
40 Good !Bad!
4 rows selected.
sql>select *
2 from rc
3 where replace(
4 translate(lower(<b>fname || lname</b>), 'abcdefghijklmnopqrstuvwxyz', rpad('a', 26, 'a')),
5 'a') is not null;
ID FNAME LNAME
--------- ------------------------------ ------------------------------
20 V%inod Testme
30 Srin* Testall
40 Good !Bad!
3 rows selected. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
