?
Solved

Formatting a field for a fixed length Flat File

Posted on 2011-05-05
8
Medium Priority
?
342 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Jeff S
  • 4
  • 3
8 Comments
 
LVL 16

Assisted Solution

by:Bryan Butler
Bryan Butler earned 1000 total points
ID: 35700313
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35700319
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35700343
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Assisted Solution

by:radcaesar
radcaesar earned 1000 total points
ID: 35700344
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
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 35700417
I think radceasar has it.  You're going to have to script something.  Can you use T-SQL?  
0
 
LVL 7

Accepted Solution

by:
Jeff S earned 0 total points
ID: 35703199
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
 
LVL 7

Author Comment

by:Jeff S
ID: 35703210
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 35726988
Thanks for the help and getting me to my final answer.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question