name to firstname and last name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

name to firstname and last name

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-06-01 : 16:40:52

i know its a simple question but iam not able to fint the soluiton
i have name and address columns which i have to break into Fname,Lname,street,city,state,zip

can anyone tell me the function or an example

thanks

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-06-01 : 16:57:31

I tryed this way but iam getting wrong output.
SELECT Right(AgencyName,CHARINDEX(' ', AgencyName)),CHARINDEX(' ', AgencyName),AgencyName
FROM dbo.Agencies

h Law 5 Eric Streich Law
ttinger 7 Morgan and Pottinger
verman Law 10 Silverman Law
d Moore 7 Blatt, Hasenmiller, Leibsker, and Moore

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-06-01 : 17:45:44

solution

SELECT substring(EmpName,1,CHARINDEX(' ', EmpName)),
substring(EmpName,CHARINDEX(' ', EmpName),LEN(EmpName)),
CHARINDEX(' ', EmpName),
EmpName
FROM dbo.TELE_CALL_LOG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-02 : 02:12:13

Read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 06:03:40

Hi vasu4us,

It looks like you need to get to grips with some string manipulation functions. Can you supply some sample data, so we can show you the kinds of things you can do to manipulate it... Thanks

And I hope you're doing this to put the data into a normalised form, as in the link Madhivanan has supplied.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-02 : 14:07:38

as a method of approach, either you have to separate first the names vs. address in the field or you can do it in one swoop. I suggest you extract first the names so that you can deal with different variant of names (LAST, FIRST or FIRST LAST etc.). By the way, if you're going to paste a sample data, include the column names and business constraints (or assumptions) in the data. TY.

May the Almighty God bless us all!

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-02 : 15:21:26

There is no 100% solution

Brett J Kaiser, ESQ 1313 Mocking bird lane, Anytown, NJ
Tara Duggan-Kizer 123 Main Street San Diego, CA

How do you parse those?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-06-02 : 17:58:22

quote:
Originally posted by X002548

There is no 100% solution

...unless he is dealing with a true comma-delimited data set and not the end-results of some poorly conceived free-text user entry form.

If the dataset is consistently comma delimited, he might be able to parse it use DTS on import, or some similar solution.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-02 : 20:04:03

Assume search for the first entry of the numeric character, assuming most of your data has a street number (if not, you have to handle them separately, either up to 3rd or 4th token, do some research about your data).

declare
@data VARCHAR(100)

SET @data = 'John Doe 123 N street'

SELECT
CASE
WHEN patindex('%[0-9]%', @data) > 0 THEN substring(@data, 1, patindex('%[0-9]%', @data)-1)
ELSE
'do something else'
END

here's a prev link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51476

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources