Formatting a field for a fixed length Flat File

I need to format a Flat File Fixed Length report for a client. In it they have allocated 12 characters for the patients phone number. In the file output, I need to format the phone exactly like this:

555/444-3333

I would like to know what would be the best way to right this value? I need to account for NULLs and if there is a NULL I need 12 blank spaces.

My field name is pp.Phone1
LVL 7
Jeff SAsked:
Who is Participating?
 
Jeff SConnect With a Mentor Author Commented:
This seems to be working for me -

CASE WHEN pp.Phone1 IS NULL Then SPACE(12) ELSE SUBSTRING(LTRIM(pp.Phone1),1,3)+'/' + SUBSTRING(LTRIM(pp.Phone1),4,3) + '-' + SUBSTRING(LTRIM(pp.Phone1),7,4) END AS [Phone],

Will split the points to be fair to all those that helped and offered a suggestion. Cheers.
0
 
Bryan ButlerConnect With a Mentor Commented:
SELECT ISNULL((SELECT SUBSTRING(pp.Phone1,1,3)+'/'+(SELECT SUBSTRING(pp.Phone1,5,3)+'-'+SUBSTRING(pp.Phone1,9, LEN(pp.Phone1) from <tablename>), "            ")) from <tablename>

The ("           ") has 12 spaces.
0
 
Bryan ButlerCommented:
This assumes the number from the DB is always ###-###-####

If it is different, like (###)###-###, then this will need a little modification.  The key is that is always the same format, otherwise this won't work.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bryan ButlerCommented:
Shoot, that won't work now that I think about it as it's always going to be adding "/" and "+" to the inside query, which makes it never null.  If it is null, this will still return "/+" i believe.  Sorry about that.  Let me see what I can figure.
0
 
radcaesarConnect With a Mentor Commented:
Use Script task in SSIS

In the ProcessInputRow method check and update the format of your phone number.

Else,

USe BCP

Load your data to a temp table

Update the phone number field as u need using cursor.
0
 
Bryan ButlerCommented:
I think radceasar has it.  You're going to have to script something.  Can you use T-SQL?  
0
 
Jeff SAuthor Commented:
I want the two individuals get the points as they deserve and helped me. My solution was the one that worked, however they assisted to get me to that point. Please make sure they are awarded the points please.
0
 
Jeff SAuthor Commented:
Thanks for the help and getting me to my final answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.