1

I have a report that shows the first four digits of SSN. However, I need to revise a report to show the last four. Nothing I have tried seems to give me the correct result. I either get an error in the report or null values. 

ToString(Chr(45)  &  right(query.ssn1, 4))

I expect the output to be:

Donald Duck SSN (123456789); I need the output to be (6789).

Dashes are not necessary.

Miguel-F
  • 13,450
  • 6
  • 38
  • 63
Christina Claflin
  • 31
  • 1
  • 1
    Given a string like "123456789", then this code `right("123456789", 4)` will return only the last four characters of the given string. What is the value of `query.ssn1` when you get the error and null values? – Miguel-F Mar 25 '19 at 19:01
  • 5
    I think the more important issue here is... why are you storing full SSNs in the DB? – Cory Fail Mar 25 '19 at 19:52
  • 3
    Or better yet, use MySQL to query only the last 4 of SSN (unless you need the full SSN) so that nothing other than MySQL has to work with that piece of data. Also keep in mind that when dealing with things like SSNs, it can get into some legal issues, which is why it's best to never let anything touch the data that doesn't actually need to. – Shawn Mar 25 '19 at 20:31
  • 2
    Or like @fyroc said, why store it to begin with? And if you need to for your application, you'll want to 1) look at using 2 db columns to store first 5 digits encrypted and last 4 separately, and 2) take a serious look at data retention policies. You can't accidentally leak data that you don't have. :-/ – Shawn Mar 25 '19 at 20:35
  • Hi thanks for responding back. I am hosted by a third party. So I cannot change the format of the DB. I can only query from it for reports. I do have a Dev site with an encryption for data security. This where I am currently testing and fixing my reports. The value of query.SSN1 is the entire 9 digits. I get an error when revising the query from 4 to any other number. (query.ssn1, 6) – Christina Claflin Mar 26 '19 at 12:39
  • What database? And if you don't need the whole SSN, don't query for it. Just get the last 4. – Shawn Mar 26 '19 at 16:48
  • Fryoc asked if I was storing full SSNs in the DB. The issue I am having is that 'right(query.ssn1, 4)' is not giving me the last four but the first four. Initially I revised it to the 'left(query.ssn1, 4) but it still gave me the first 4. – Christina Claflin Mar 26 '19 at 17:26
  • 2
    @ChristinaClaflin What database system are you using? `right(query.ssn,4)` should return the 4 right-most characters of your input string (`query.ssn1`). Does `query.ssn1` return more than 4 digits? Is the query, by chance, already returning only the first 4 digits? – Shawn Mar 26 '19 at 17:44
  • I am using Microsoft SQL Server Management Studio 2014. Yes, I agree, this string should return the last four of the integer. Right now, the query is returning the first four with the following string ToString(Chr(45) & right(query.ssn1, 4) )...which it really should be returning the last four. Do you agree? – Christina Claflin Mar 26 '19 at 21:12
  • If the query is returning the first 4 digits, it is using the wrong function - left - i.e. returns characters starting from the beginning of the string. I'd be very concerned about a host that stores social security numbers in plain text, to be accessed by anyone. Not to mention ssn's are now leaking into log files due to the errors. Sure hope they don't your cc number! – SOS Mar 27 '19 at 00:25
  • Yes, I agree. I tried contacting them but since the report was custom built prior to me coming to the site they will not look at the code. Any ideas for a workaround? – Christina Claflin Mar 27 '19 at 15:23
  • Sorry but there is absolutely no way a *sql* query using right(columnName,4) could return the first 4 characters of a 9 digit string. So something must be different than described. 1. Are you using the function in your *database* query (not Qoq or the report) 2. Did you alias the column in your query? 3. What is the column data type in the database. – SOS Mar 27 '19 at 15:58

0 Answers0