Help needed! Need to force leading whitespace characters on SQL import! =\

I know that this almost never happens... but Im dealing with an AIX flatfile-database conversion that brings 80 tables into SQL. Im not allowed to touch this stuff or to massage how its brought over to SQL, as it deals with medical records...

What I need to do though, is on creating my own table imports, for my "account" fields to match with the existing SQL conversion table "account" fields, I have to match requirements...

Existing account numbers are a total of 6 charaters. Account numbers with less than six characters contain leading whitespace character equivalents to total the six character spaces for "account number"

When I import records, I need to force the same requirement and have a min and max length of characters for "account number" = 6 characters and any account number less than 6 characters must also have the necessary whitespace character equivalent added to it.

How would I do this? It needs to be automated, as this is a process that will run nightly and cannot have a human sitting on it every day, 7 days a week... I cannot accurately join unless I can meet this requirement and my hands are tied because I can't change the way the formatting is done on the imported tables =(

Any help would be greatly appreciated... I'm quite stuck

Heh, well, I would go and tell whomever you talked to that said you can't play with the data "because it's medical records" to go fly a kite. There is nothing special about medical records beyond HIPAA, and that says absolutely nothing about what format medical data must be stored in. Either there is another reason you aren't allowed to space-trim something as simple as the account number, or the person who said that actually believes it.

When you import your records, you can tell it to import left(space(6)+fieldname,6) and that'll left pad the field to 6 places. Or you can do the same thing on your joins:

SELECT *

FROM Table1

JOIN Table2 ON table1.field1=left(space(6)+table2.field1,6)

Of course, the joins will run extremely slow, but that's what you get when you take an originally crappy format, then try to force SQL Server to work with said crappy format -- Crappy performance, and crappy code.

|||

Motley wrote:

Heh, well, I would go and tell whomever you talked to that said you can't play with the data "because it's medical records" to go fly a kite.

Oh man, dont' temp me... I was working till midnight last night manually creating what would have been a flawlessly executing DTS package because of this... =\

Motley wrote:

Of course, the joins will run extremely slow, but that's what you get when you take an originally crappy format, then try to force SQL Server to work with said crappy format -- Crappy performance, and crappy code.

Lol, yes...

Thanks for the suggestion, I'm going to try to implement that right now. Appreciate your quick response!

|||

Hmm... it pushes everything six digits over... how do I prevent that?

Here's how my select reads:

SELECT Accession,left(space(6)+SUBSTRING(PatNo, 0,CHARINDEX('.', PatNo)),6)as AccountKey,RTRIM(SUBSTRING(PatNo,CHARINDEX('.', PatNo) + 1,LEN(PatNo)))as DependentKey, LastwordNum, LastName, FirstName, DOB, ExamDate, ExamCode, Exam, OrderMD, ReadMD, CPTCode, DiagDesc, ExamStatus, Department

When I run it, it shoves the number over... so a number that would read "123456" becomes blank or null =\

|||Motley meant to tell you to use RIGHT(), not LEFT().

SELECT Accession,RIGHT(SPACE(6)+SUBSTRING(PatNo, 0,CHARINDEX('.', PatNo)),6)as AccountKey,RTRIM(SUBSTRING(PatNo,CHARINDEX('.', PatNo) + 1,LEN(PatNo)))asDependentKey, LastwordNum, LastName, FirstName, DOB, ExamDate,ExamCode, Exam, OrderMD, ReadMD, CPTCode, DiagDesc, ExamStatus,Department

To make things easier for you, you might consider creating a UDF calledfnPadLeft which you can use each time you need that functionality. Here's a simple version, to which error checking code should be added:

CREATE FUNCTION dbo.fnPadLeft (@.myStringIN varchar(200),@.myPadCharacter char(1),@.myPadCount int)
RETURNS varchar(200) AS
BEGIN
DECLARE @.myStringOUT varchar(200)
SELECT @.myStringOUT = RIGHT(SPACE(@.myPadCount)+@.myStringIN,@.myPadCount)
RETURN @.myStringOUT
END

Usage example:

SELECT dbo.fnPadLeft('Terri',' ',10)

|||LOL, oops, my bad. Tmorton is right, I meant RIGHT(Space(6)+field,6) not LEFT(space(6)+field,6)