Splitting a column up .... 1

[rabinski]

Hi guys -

I have a column with 'Mr Joe Bloggs' and need to split into Title,Forename and Surname ...

Coming from a perl backgound I would use regex - but anyway I am looking at CHARINDEX and SUBSTRING -

I just need to split on the space .....

Any helpful tips would be most excellent !

Thanks in advance ...

R

 

[Catadmin]

You're probably right. Other than PATINDEX (which only returns the first instance of a pattern, these would be the two to use.

So, maybe some code like:

Declare @Space1 int,
        @Space2 int,
        @Title varchar(4),
        @FName varchar(25),
        @LName varchar(25)

Set @Space1 = CHARINDEX(' ',NAME,0)
Set @Space2 = CHARINDEX(' ',Name,@Space1+1)
Set @Title = Substring(NAME,0,@Space1)
Set @FName = Substring(NAME,@Space1+1,@Space2)
Set @LName = Substring(NAME,@Space2,<Value>)

<Value> should be the actual column size. Like 50 if NAME col. is a char(50) datatype.

Does this help?






Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

I found this link which may help:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId=5

 

[Catadmin]

HEY! Thanks, jbenson001! I appreciate you posting the link.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

no problem.. hope it works for you