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 Thu, 07 August 2014 11:46
 tuckersuz Messages: 26Registered: 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
 Bill B Messages: 1662Registered: December 2004 Senior Member
select ssn
from my_table
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
 EdStevens Messages: 819Registered: 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
 Solomon Yakobson Messages: 2622Registered: January 2010 Location: Connecticut, USA 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
 tuckersuz Messages: 26Registered: 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
 tuckersuz Messages: 26Registered: 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: Thu Aug 17 04:46:16 CDT 2017

Total time taken to generate the page: 0.10446 seconds