ANYTHING THAT CAN HELP TRIM THE DATA FOR A GIVEN STRING...

Question

ANYTHING THAT CAN HELP TRIM THE DATA FOR A GIVEN STRING...

Viewing 7 posts - 16 through 21 (of 21 total)

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

David Lester Hall of Fame Points: 3647 More actions · Answer 1

Thanks, I had also reached the conclusion I would have to have a way to know the entry was a person vs not a person. It occured to me someone might have a different way of looking at the problem.

Those on this board seem to be creative enough to solve almost anything. So once I am out of ideas, I see if any here have ones I had not thought of.

:w00t:

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

Jack Corbett (9/8/2009)
Darn it Jeff, I know I have seen that solution somewhere before, don't know if it was by you or not, but I wish I would have remembered it.:-D

Might be me... I've posted just the code before. This time, I've just posted the explanation. I think Peter Larson and Michaeal Valentine Jones have also used it on these forums. I saw it somewhere else that predated all of us... Just can't remember off the top of my head. It also had the problem of requiring expansion of the original string size which this rendition doesn't experience.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

David Lester (9/8/2009)
Thanks, I had also reached the conclusion I would have to have a way to know the entry was a person vs not a person. It occured to me someone might have a different way of looking at the problem.
Those on this board seem to be creative enough to solve almost anything. So once I am out of ideas, I see if any here have ones I had not thought of.
:w00t:

There is a way if there is any consistancy to the data at all. A mid word split of a company name (according to your data example), will have a space followed by a lower case character. Enter the savior known as COLLATE. I don't have access to an SQL Server this week so pardon any errors I may have in the code... I'm doing it just from memory...

SELECT CASE

WHEN String2 COLLATE LATIN_BINARY LIKE ' [a-z]% COLLATE LATIN_BINARY

THEN String1+LTRIM(String2)

ELSE String1+String2

END AS FullName

FROM dbo.YourTable

I don't remember the actual name of the collation so LATIN_BINARY will likely be incorrect but it will be something similar. I may also have the word COLLATE in the wrong position, but you get the idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

Heh... of course, the proper way to fix this would be to correctly normalize the data with a separate company name column. But, I suspect that's not currently an option.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Lester Hall of Fame Points: 3647 More actions · Answer 5

That is great Jeff, yes the data would have a space lower case character, I missed that pattern.

Oh, I so wish the source database was normalized and data corrected. The source is a vendor application, which is far from normalized, or consistant. (Yes, in the recent past IT pushed toward getting a new vendor... those people no longer work here...*ouch*) Even having the data entered consistantly is at 80%. The newest attempt on my part at getting the data fixed, is what I like to call the "nagging report" 😀

Everyday everyone in a give department receives a separate email for each type of data problem my verification jobs detects. If an explanation (repeated) of why it needs to be correct data fails, nag the entire group responsible for it, there is usually at least one who wants the nagging to stop. 😎

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Heh... oh, be careful, David. While it's true that sometimes the "Squeaky wheel gets the grease", it's also true that sometimes the "Squeaky wheel gets greased" if you understand the difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Lester Hall of Fame Points: 3647 More actions · Answer 7

Jeff, very very true. It is so much more fun to be the squeaking wheel when one is given it as a continuing goal each year. Ahh, all the politics of a company 🙂

Of course, if I do miss seeing the line to not cross, IBM did just move into town...a small town very short on experienced technical people. 😉