View previous topic :: View next topic
|
Author |
Message |
bbharathiraj Warnings : 1 New User
Joined: 26 Oct 2006 Posts: 50 Location: Chennai
|
|
|
|
Dear Everybody,
Please help me to resolve the below issue.
I have one table with 2 columns. Both columns having some non alphanumeric values. (other than A-Z and 0-9 and space).
I would like to find all the rows if any of the columns(col1,col2) value (character by character) is not A-Z or 0-9 or spaces.
For example,
Code: |
Col1 Col2
----------- --&..JOHN
USA SMITH
--^&INDIA CATHY
GERMANY ---%$LARA |
in output, I want to fetch the below rows.
----------- --&..JOHN
--^&INDIA CATHY
GERMANY ---%$LARA[/code]
Also is there anyway to update all these character (other than A-Z,spaces,0-9) to spaces.
The below query can use to fetch the values only non alpha (not A-Z) from only one column.
SELECT COL FROM
(
SELECT COL,
CASE TRANSLATE(UPPER(COL), ' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ' '
THEN 1
ELSE 0
END AS VAL
FROM TABLE
) AS TEMP
WHERE VAL = 1 ;
I would like to know the query to fetch rows by checking both col1,col2.
Also please provide me the query to update all the invalid character(other than A-Z and 0-9) in all rows to spaces. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as you guessed already,
If there is any volume at all (over 1000 rows on table) your best bet is to dump the file and use SORT, REXX or COBOL to change, then reload,
send a PM to Dick and ask him to lock this thread and reopen your SORT thread |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Anticipated and done
The other topic has been re-opened. . .
d |
|
Back to top |
|
|
|