OraFAQ Forum: SQL & PL/SQL » Find the number 3 in the third and last digits of an SSN

Home » SQL & PL/SQL » SQL & PL/SQL » Find the number 3 in the third and last digits of an SSN (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find the number 3 in the third and last digits of an SSN [message #621007]

Thu, 07 August 2014 11:46

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

Report message to a moderator

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: 1971
Registered: December 2004

Senior Member

select ssn
from my_table
where lpad(ssn,'0',9) like '__3%'
or lpad(ssn,'0',9) like '%3'

Report message to a moderator

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: 1376
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.

Report message to a moderator

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: 3273
Registered: 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: 26
Registered: July 2009

Junior Member

Hi Bill:

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

Report message to a moderator

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: 26
Registered: July 2009

Junior Member

Thanks to everyone for the suggestions. Using LPAD (0,9) worked.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:53:55 CDT 2024