Hello all, I am in the process of building out a report to retrieve Customer data for my institution that uses Social Security Number. The issue is that the data item is stored with stripped leading zeroes and I need the full social security number.
For example: 012345678 is stored as 12345678.
I am building the report to hopefully use as a reference item to assist with back end imaging using autofill from a text file.
Is there an elegant way to have leading zeroes added and make sure the data item does not exceed nine characters?
For now, the only solution I could think of was to Cast Social Security Number to varchar and then build out a Case statement from there using concatenation to join the items together.
cast([Social Security Number], varchar(10))
case
when cast([Social Security Number], varchar(10)) = '0' then '000000000'
when cast([Social Security Number], varchar(10)) in ('1','2','3','4','5','6','7','8','9') then '00000000' || cast([Social Security Number], varchar(10))
etc.
I'm guessing there's an easier way to do this and I'm just not thinking my way through it very well.
Any assistance you could give would be greatly appreciated.
Thank you,
sjdig
Try the LPAD() function: https://www.ibm.com/support/knowledgecenter/en/SSEP7J_8.4.0/com.ibm.swg.im.cognos.fnct_ds.8.4.0.doc/fnct_ds_id5734LPad.html
The examples in that documentation are helpful. Hope this helps!
Quote from: psrpsrpsr on 28 Feb 2017 12:41:47 PM
Try the LPAD() function: https://www.ibm.com/support/knowledgecenter/en/SSEP7J_8.4.0/com.ibm.swg.im.cognos.fnct_ds.8.4.0.doc/fnct_ds_id5734LPad.html
The examples in that documentation are helpful. Hope this helps!
Thank you for the suggestion. It looks as though LPAD does not work for my institution currently. Our version of Cognos was through a reseller so functionality may be limited compared to the full suite.
Any other suggestions would be welcome.
I figured it out. I was just thinking backwards in my solution.
Instead of my initial case function, I was able to use the following:
case
when [Social Security Number] between 0 and 9 then '00000000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 10 and 99 then '0000000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 100 and 999 then '000000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 1000 and 9999 then '00000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 10000 and 99999 then '0000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 100000 and 999999 then '000' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 1000000 and 9999999 then '00' || cast([Social Security Number], varchar(10))
when [Social Security Number] between 10000000 and 99999999 then '0' || cast([Social Security Number], varchar(10))
else cast([Social Security Number], varchar(10))
end
This was much easier than how I anticipated needing to solve the issue. Hope it can help someone else too! :)
Quote from: sjdig on 28 Feb 2017 12:50:10 PM
Thank you for the suggestion. It looks as though LPAD does not work for my institution currently. Our version of Cognos was through a reseller so functionality may be limited compared to the full suite.
Any other suggestions would be welcome.
substring('000000000' || [Social Security Number], (char_length ([Social Security Number]) + 1) , 9)
Automatically adds 9 leading zeros to social security number. It then takes the number of characters in SSN, adds 1 for starting position, and grabs the 9 characters.
If it was 4 character SSN, it would grab the zeros starting at position 5 from (000000000XXXX), resulting in 00000XXXX.
QuoteCode: [Select]
substring('000000000' || [Social Security Number], (char_length ([Social Security Number]) + 1) , 9)
Automatically adds 9 leading zeros to social security number. It then takes the number of characters in SSN, adds 1 for starting position, and grabs the 9 characters.
If it was 4 character SSN, it would grab the zeros starting at position 5 from (000000000XXXX), resulting in 00000XXXX.
Now that is just genius...I really like that, and I can think of lots of situations where that would come in handy. My first thought was the case statement that sjdig put up, but this is much more elegant.
Thanks for posting that.
Quote from: BigChris on 01 Mar 2017 02:01:01 AM
Thanks for posting that.
My pleasure! Thanks for the kind words.
Quote from: CognoidJay on 28 Feb 2017 01:11:49 PM
substring('000000000' || [Social Security Number], (char_length ([Social Security Number]) + 1) , 9)
Automatically adds 9 leading zeros to social security number. It then takes the number of characters in SSN, adds 1 for starting position, and grabs the 9 characters.
If it was 4 character SSN, it would grab the zeros starting at position 5 from (000000000XXXX), resulting in 00000XXXX.
Thanks, this solution also worked. I did have to slightly modify the formula though as Social Security Number was stored as a number in my database.
substring('000000000' || cast([Social Security Number], varchar(10)), (char_length (cast([Social Security Number], varchar(10))) + 1) , 9)There's always more than one way around a problem!