>
> 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
Shrikant,
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),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
' ')))
FROM TABLE_NAME;
* 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 -
SELECT SUBSTR(, INSTR( (TRANSLATE(,
'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 !
Regards,
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