Why Am I an Error with this Query?

dvdaddict32 · #1 (**permalink**) 08-25-11, 10:28

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?

sathyaram_s · #2 (**permalink**) 08-25-11, 10:33

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

tonkuma · #3 (**permalink**) 08-25-11, 10:38

Quote:

It looks like this function only works on character fields.

You are right!

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech