If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Add Leading Zeroes to a data item?

Started by sjdig, 28 Feb 2017 12:37:25 PM

Previous topic - Next topic

sjdig

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

psrpsrpsr


sjdig

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.

sjdig

#3
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!  :)

AnalyticsWithJay

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.

BigChris

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.

AnalyticsWithJay


sjdig

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!