Friday 19 July 2013

Query to find the Special Characters in PS_NAMES table

SELECT N.EMPLID,N.NAME_TYPE,N.EFFDT,N.NAME,TRANSLATE(UPPER(N.NAME)
,'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' ') AS "SPECIAL"
FROM PS_NAMES N
WHERE N.EFFDT=(SELECT MAX(N1.EFFDT)
FROM PS_NAMES N1
WHERE N1.EMPLID=N.EMPLID
AND N1.NAME_TYPE='PRI'
AND N1.EFFDT<=SYSDATE)
AND N.NAME_TYPE='PRI'
AND RTRIM(LTRIM((TRANSLATE(UPPER(N.NAME),'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' '))))<>' '
ORDER BY N.EMPLID

No comments:

Post a Comment