Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Why Am I an Error with this Query?

    Hey guys,

    I am trying to run a query that will return me just the last four digits of a person's social security number. I tried the following in my SELECT statement:


    SELECT Distinct right(A.MBR_SSN_NBR, 4 ) as SSN


    Unfortunately, I get an error complaining that the data type or length of RIGHT is incorrect.

    It looks like this function only works on character fields. If I use


    SELECT Distinct right('A.MBR_SSN_NBR', 4 ) as SSN


    The query runs and returns _NBR.



    IS there something I'm missing here or is there a way to convert the SSN to character in my query?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You should use
    Code:
     
    right(char(A.MBR_SSN_NBR),4)
    As a hack, you can also use
    Code:
     
    MOD(A.MBR_SSN_NBR,10000)
    I assume the latter one will be more efficient

    HTH
    Last edited by sathyaram_s; 08-25-11 at 11:37. Reason: removed [quote] and added [code]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It looks like this function only works on character fields.
    You are right!

    ... is there a way to convert the SSN to character in my query?
    You may want to use DIGITS function like ...
    Code:
    SELECT DISTINCT
           RIGHT( DIGITS(a.mbr_ssn_nbr), 4 ) AS ssn
    ...
    Note: function CHAR(number) may return a value with left justified.
    So, RIGHT( CHAR(...), 4 ) may return a value including some trailing blanks.
    Last edited by tonkuma; 08-25-11 at 11:45. Reason: Add note.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •