> Hi Ari,
> I need your help in coming up with a solution for the following problem -
> A table has a varchar2 column of width - 250. This comment column can be 
> used to store the social security number along with the user comments. I 
> need to determine if a column value has the SSN included. The user 
> entered comments will not contain any numerical information. I was 
> planning to determine the position of occurence of the first number and 
> then use substr to obtain the SSN, but was unable to come up with a SQL 
> statement which could do the job. 			
> Thanks in anticipation
> Shrikant R Sawant
> GE Corporate Research and Development 
> Schenectady, Albany NY

Based on your note that no numerical information will be entered except for the
SSN, here is a solution:

SELECT ltrim(rtrim(translate(upper(SSN_COLUMN),
'                          ')))

* The "upper" function converts the column to upper-case, which simplifies the
  "translate" function from repeating each alphabetical character in both upper
  and lower case.
* The "translate" command replaces all alphabetical characters with a space. If
  there will be other characters, such as !@#$%^+=,. and so on, add them to the
  translate function.
* The "ltrim" and "rtrim" commands will remove the spaces created by the
  "translate" function.

Hope that this helps!

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

(Note: Shrikant came up with another solution; Ari...)

Hi Ari,
Thanks for your prompt response. I took inspiration from your solution
and attempted to make it more generic so as to ensure that alphabetic (
upper as well lower ) and alphanumeric characters are taken care of with
minimal amount of hard-coding.

I came up with the following SQL statement -

'012345678', '999999999')), '9',1,1), 9 )
FROM table_name;

Translate will convert all numeric characters to 9 and instr will give me
the location of the first 9 ( effectively the location of the first 
numeric ). A substr can then be used to extract the 9 SSN digits starting
from the position reported by instr.

I believe that the above SQL should work for any combination of user
entered comments.

Thought that I would share the modified SQL with you.

Thanks again !

Shrikant R Sawant

GE Corporate Research and Development
Schenectady, Albany NY
<-> For 245+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page width="16" height="18">Back to Ari Kaplan's Home Page