Home » SQL & PL/SQL » SQL & PL/SQL » Find the number 3 in the third and last digits of an SSN (oracle 11g)
Find the number 3 in the third and last digits of an SSN [message #621007] Thu, 07 August 2014 11:46 Go to next message
tuckersuz
Messages: 26
Registered: July 2009
Junior Member
Hello:

This is a quick question someone might be able to answer quickly, since I didn't see it on any other posts. What is an easy way to find the number 3 in the third and last digits of a Social Security Number,without dashes, keeping in mind that an SSN may be 8 digits long (since the SSN can start with a zero) or 9 digits long?

Thanks

Re: Find the number 3 in the third and last digits of an SSN [message #621009 is a reply to message #621007] Thu, 07 August 2014 12:20 Go to previous messageGo to next message
Bill B
Messages: 1100
Registered: December 2004
Senior Member
select ssn
from my_table
where lpad(ssn,'0',9) like '__3%'
or lpad(ssn,'0',9) like '%3'
Re: Find the number 3 in the third and last digits of an SSN [message #621011 is a reply to message #621007] Thu, 07 August 2014 12:48 Go to previous messageGo to next message
EdStevens
Messages: 286
Registered: September 2013
Senior Member
tuckersuz wrote on Thu, 07 August 2014 11:46
Hello:
keeping in mind that an SSN may be 8 digits long (since the SSN can start with a zero) or 9 digits long?


Actually, you just hit on an important concept concerning data type.

In truth, SSN is NOT a number. It is a character string which, by usage specification, is limited to only those characters that we also use to represent numbers.

In a true number, leading zeros are non-significant. That's why, in the treatment of numbers, they are dropped. In a SSN, being a code and not a number, that leading zero is very much significant.

And as a result of the above, storing an SSN as a NUMBER data type is just as wrong as storing a number or a date as a CHARACTER data type, or storing a date as a NUMBER data type.

Look at the nature of the data, not its character representation, and store it in the appropriate data type.
Re: Find the number 3 in the third and last digits of an SSN [message #621019 is a reply to message #621009] Thu, 07 August 2014 13:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
Without converting to string:

select  ssn
  from  my_table
  where trunc(ssn / 1000000)  - trunc(trunc(ssn / 1000000) / 10) * 10 = 3
    and ssn - trunc(ssn / 10) * 10 = 3
/


SY.

[Updated on: Thu, 07 August 2014 14:01]

Report message to a moderator

Re: Find the number 3 in the third and last digits of an SSN [message #621233 is a reply to message #621009] Mon, 11 August 2014 16:51 Go to previous messageGo to next message
tuckersuz
Messages: 26
Registered: July 2009
Junior Member
Hi Bill:

Thanks for the response. That worked (using LPAD).

Re: Find the number 3 in the third and last digits of an SSN [message #621234 is a reply to message #621019] Mon, 11 August 2014 16:53 Go to previous message
tuckersuz
Messages: 26
Registered: July 2009
Junior Member
Thanks to everyone for the suggestions. Using LPAD (0,9) worked.


Previous Topic: Window Functions = Analytic Functions in Oracle RDBMS?
Next Topic: joining between 2 different dates ?
Goto Forum:
  


Current Time: Tue Sep 23 07:22:49 CDT 2014

Total time taken to generate the page: 0.05613 seconds