Better Way

[dzdncnfsd]

How can I improve this code by using variables for FirstSpace and NextSpace? It works as is - I just am trying to learn something.

SELECT Space(13)+[CUS-LOAN] AS ACCOUNT, 
Right("0000000000" + cast([FIC-AMT-PAST-DUE]*100 as varchar),10) AS AMOUNT_DUE,
[FIC-SOCSEC] AS SSN, 
CHARINDEX(space(1), [FIC-NAM1]) AS FirstSpace,
CHARINDEX(space(1),[FIC-NAM1], CHARINDEX(space(1),[FIC-NAM1])+1) AS NextSpace,
[FIC-NAM1],
Left(Left([FIC-NAM1],CHARINDEX(space(1),[FIC-NAM1])-1)+ Space(16),16) AS FirstName,
CASE
 WHEN CHARINDEX(space(1),[FIC-NAM1], CHARINDEX(space(1),[FIC-NAM1])+1) = 0 THEN
      LEFT(LTrim(Substring([FIC-NAM1],CHARINDEX(space(1),[FIC-NAM1])+1,30)+ space(30)), 30)
 WHEN CHARINDEX(space(1),[FIC-NAM1], CHARINDEX(space(1),[FIC-NAM1])+1) <> 0 THEN
      LEFT(LTrim(Substring([FIC-NAM1],CHARINDEX(space(1),[FIC-NAM1], CHARINDEX(space(1),[FIC-NAM1])+1),30)+ Space(30)),30)
END AS LastName,
Left([FIC-NAM3] + Space(30),30) AS Address, Space(30) AS ADDRESS2, [FIC-NAM5], 
Right(LTrim(RTrim([FIC-NAM5])),2) AS STATE, 
Left(Left([FIC-ZIP],5) + &quot;-&quot; + Substring([FIC-ZIP],6,4) + &quot;0000&quot;,10) AS ZIPCODE, [FIC-CLOS]
FROM ShawMiniMaster_Current
WHERE [FIC-CLOS]='0'

Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[fluteplr]

First find the person who designed this database structure, get a copy of Codd on database design and make them read it over and over again.

 

[Liberty]

Have you consider using a cursor?

 

[dzdncnfsd]

Thanks for the replies, but I am the one who wrote the code, and I did it that way because I didn't know of a better way, which is why I was asking for help.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[redlam]

hi dzdncnfsd.
can you provide a little more information on FirstSpace and NextSpace? are you just trying to parse out the first and last names from a name field? what are some of the typical values for these fields?

-michelle

 

[dzdncnfsd]

Hi, Michelle. Yes, thanks, I am trying to parse out first and last names, and the problem is that there can be such a variance in them. For instance:
John Doe
John Doe
John D. Doe
John Doe Jr.
Mary Jane Van Horn

I want to create a stored procedure (that can be called from a scheduled job) to export this info from SQL into a fixed length text file that will be FTP'd to another server. The existing code works, but I know it isn't the best way to do it.
Thanks again.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[redlam]

sry gladys - started working out a solution for you but got caught up in another project.
the biggest problem you're facing is that the name field is completely unpredictable which makes it impossible to make assumptions like 'there will be one space after the first name followed by the middle initial, another space and then the last name' (duh, i'm not telling you anything new). anywho - the code you have seems to work for most cases but not all. try it with this name 'Mary Anne Von Trappe'. i'll continue plugging away at a solution but in the meantime here's my thought (in case anyone has a better plan):
create a cursor to step through each name
create a loop for the length of the name that determines the position of each space
parse the name at each space position

 

[magicrat]

To really do this with any high degree of accuracy is an enormous undertaking. However, there may be a simple solution because your file is in text form when it is transferred. There are a number of commercial software packages available that do this with varying degrees of sophistication. Group1 Software, FirstLogic, Mailer's+4 and RightFielder would be a few names to get you started. This process is most commonly and accurately done in the preparation of Direct Mail data files, so a broader search on Direct Mail software would give you all of the options. These packages cost anywhere from $250 - $100,000+ and cost does not necessarily correlate to accuracy of results when parsing Names. Mailer's+4 has a variety of reasonably priced packages aimed at data quality, so if you can afford to risk a few hundred bucks on the problem, I'd start there.
I've written a number of these routines to process files of up to 100 Million names in everything from IBM Assembler to Transact SQL, so if you really want to write something yourself, I'd be happy to give you a functional overview on how to do it.

 

[redlam]

i think magicrat is right - even if we parse each name, we would still need some heavy duty criteria for determining whether the name is part of the first, last or middle name. for example - 'John Paul Von Trappe Sr.' would be easily parsed into 5 names but determining programatically that 'John Paul' is the first name and 'Von Trappe Sr.' is the last name seems nearly impossible. if the existing code is sufficient for your purposes you may want to consider leaving it as is...
if you are interested in simply parsing the names, let me know and i'll post something for you.
good luck!

 

[dzdncnfsd]

Thanks so much for all the help. I think we have decided to keep on using what we have. The margin of error is pretty low as is, so we will just make do.
Thanks,

Gladys Clemmer
gladys.clemmer@fifsg.com