Identify Social Security Numbers

How will you verify that there are no social security numbers greater than or less than 9 digits long in a table called customer_info?
Write the SQL query that will identify any social security numbers that do not meet this criteria

Questions by preetigupta107

Showing Answers 1 - 12 of 12 Answers

nicel007

  • Mar 4th, 2011
 

Try This one
 
    SELECT
               A.SSN_NO,
               A.SSN_NO_LENGHTH
     FROM
             (
               (
                SELECT
                           MAX(LENGTH(SSN_NO)) AS SSN_NO_LENGTH, 
                           SSN_NO
                FROM
                        CUSTOMER_INFO
                GROUP BY
                              SSN_NO
                 )
               UNION
               (
                 SELECT
                            MIN(LENGTH(SSN_NO)) AS SSN_NO_LENGTH,
                            SSN_NO
                  FROM
                           CUSTOMER_INFO
                  GROUP BY
                             SSN_NO
                 )
            ) A
WHERE
    A.SSN_NO_LENGTH !=9

  Was this answer useful?  Yes

devgan

  • Mar 5th, 2011
 

You can use like operator to search five digit no.i am givinng you an example...

select Name from EmpName where Name Like '12___'.

You have to use a Wild Character _ after any digit.The use of Wild character is to allow to match any single character after any digit.so i have use three _ after that....

Thanks,
Nishant kr.

  Was this answer useful?  Yes

devgan

  • Mar 5th, 2011
 

select ID from test where ID >9999 and ID <100000

i.e. select coulmnname from table where columnname>9999 and name < 100000

This would work definetely.....

  Was this answer useful?  Yes

RamThiru

  • Nov 26th, 2014
 

Code
  1.  

  2.  

  3. SELECT ssn_no, LENGTH(ssn_no) FROM Customer_Info WHERE LENGTH(ssn_no) != 9



if the result returns= 0,Pass
else Fail

  Was this answer useful?  Yes

bhaskar

  • Jun 22nd, 2015
 

This Mentioned Query will return the value
between 99999 and 100000
But the question is
to find the customer id which should not exceed 9 digit.
,,Please provide me bit more clarity in the Query
Thanks
Bhaskar Rao

  Was this answer useful?  Yes

vivek

  • Jul 2nd, 2015
 

Performance wise:
Assuming ssn_no is primary key for the table customer_info
SELECT COUNT(ssn_no) FROM customer_info
WHERE LENGTH(ssn_no)!=9;

  Was this answer useful?  Yes

Shyamala

  • Aug 7th, 2015
 

Code
  1. SELECT * FROM CUSTOMER_INFO WHERE SSNID NOT IN ( SELECT SSNID FROM CUSTOMER_INFO WHERE LENGTH(SSNID) = 9)

  Was this answer useful?  Yes

Manish Rana

  • Aug 7th, 2015
 

We can find the records of which the length is <9 or >9. By this we can identify we such type of records exists in the table or not.
If the database is Oracle then use the below query:

Code
  1. SELECT * FROM customer_info WHERE length(SSN_No)<9 OR length(SSN_No)>9

  Was this answer useful?  Yes

Poorva

  • Aug 12th, 2015
 

We need to use len and not length in the syntax

  Was this answer useful?  Yes

Deepak

  • Aug 13th, 2015
 

Use Not Equal to instead

Code
  1. SELECT * FROM customer_info WHERE length(SSN_No)<>9

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions