Help to Select from 3 fields - no nulls & no blank strings

Question

Post reply

Help to Select from 3 fields - no nulls & no blank strings

LMeyer

SSC Enthusiast

Points: 143
More actions
February 10, 2009 at 2:17 pm

#200052

I know how to eliminate the NULL records, but when I run my select statement, I'm still receiving a lot of blank records. What I need to accomplish is pulling the following fields from the table Clients without having any blank records. In other words, I do not want the name(cltattn) if there is no email. I also do not want the email if there is no name(cltattn).
ID
CltAttn
CltEmail
Here is my simple query:
select ID, cltattn, cltemail from clients where cltattn is not NULL and cltemail is not NULL
The second part of this is to place the data gathered into another table where the name field is two separate fields.
Please help!
LMeyer

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

When you say blank, do you mean an empty string? If so, try:

select ID, cltattn, cltemail from clients where cltattn is not NULL and cltemail is not NULL

SELECT ID

,cltattn

,cltemail

FROM dbo.clients

WHERE COALESCE(cltattn, '') <> ''

AND COALSCE(cltemail, '') <> '';

Note - the above will not use any indexes because of the function on the columns.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

LMeyer SSC Enthusiast Points: 143 More actions · Answer 2

Thanks for the reply!

Here is what I get when I run your select statement:

Msg 195, Level 15, State 10, Line 6

'COALSCE' is not a recognized built-in function name.

Do I need to install something else to have that function available?

Thanks,

LMeyer

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Sorry - that is a typo, it should be COALESCE.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

LMeyer SSC Enthusiast Points: 143 More actions · Answer 4

Thanks, Jeffrey. I should have noticed that, too.

That works wonderfully!

Now, the second part of my task is to take the place the name and email in another table (they both use the ID field as a key). The difficult part is that the original name field is all one string (cltattn). I have to place it in a fname and lname fields. As you can expect, the original name field (cltattn) is all over the board. Here are some examples of the types of entries in the field:

DR fname lname

fname middleInitial & fname middleInitial lname

MR fname lname

fnameInitial lname

fname lname OR fname lname

fname lname CFO

fname middleInitial lname DC

fname lname CPA

fname & fname lname

MS fname middleInitial lname

fname middleInitial lname III

c/o fname and fname lname

I know we will not have 100% results, but we just want to try to get the best we can into the new fields. Any suggestions?

Thanks so much!

LMeyer

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

What you have left to do is not going to be easy. Especially since you have no reliable method of determining that the first element in the space delimited list is a firstname or lastname or honorific.

For something like this, it might be worth the cost to purchase the code from a company that specializes in this kind of thing. The one I know of is called Melissa Data - and they have components that work directly in SQL Server.

Good luck.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jfletcher SSC Veteran Points: 271 More actions · Answer 6

I second Jeffrey's suggestion. I have the Melissa Data software and just recently used it to fix names that were in lname fname mname format. Go to MelissaData.com for more info.

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 7

Although I agree with the guys that why try re-invent the wheel when someone or some company has already done this and can solve you problem simply, sometimes the resources for getting the product are not available and therefore you have to do this yourself. I have come across this many time in my career and a process of cleansing the data would have to be endured if you do this yourself.

This process wouldn't be automated though, as from what you posted the data doesn't follow any consitant pattern.

A few thing to try is to remove all the characters you don't need, i.e. special characters like & and things like c\o. titles are easily identified and are easily removed from the data. You then have to make a call on how the majority of the data looks and if there is fname, mname and lname in the majority. Use substring and move each substring into the relevant fields. if there is only fname lname you can shift the lname out of mname into lname if lname is blank.

I wont want to write the code for you as your data is unique but maybe I've given you a starting point.

LMeyer SSC Enthusiast Points: 143 More actions · Answer 8

Thank you all for your sugguestions. I will have to check with the requestor on stripping out C/O's, etc. We will definitely need to modify the data somehow to move forward with this.

Thanks again!

LMeyer