help required plz

Question

Post reply

help required plz

faruk.arshad

SSC Veteran

Points: 262
More actions
September 12, 2011 at 5:48 am

#247542

i have this string NAme :"Syed Abu Farooq"
i want to break this into 3 strings and make its three colums
like
FirstNAME MiddleName LAstName
======= ========== =========
Syed Abu farooq
please help me out ...

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could use the splitter function you can find here:

http://www.sqlservercentral.com/articles/tally+table/72993/

Read the whole article, it really is worth the time.

If you need help on how to use the code, just ask and I'll be glad to help.

-- Gianluca Sartori

PB_BI SSCoach Points: 17463 More actions · Answer 2

A fairly simple solution could be written to do this involving SUBSTRING and CHARINDEX but that is provided the data will always be in the format of "firstname[space]middlename[space]lastname".

for example if sometime the middle name isn't there then it becomes trickier.

Is the data likely to be erratic?

I'm on LinkedIn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

How would you handle things like these:

John van Wyk (van Wyk is the surname)

David West Jnr (West is the surname)

etc...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

faruk.arshad SSC Veteran Points: 262 More actions · Answer 4

yes ..it will always have space ......the only constant thing is space after every word....

wat will b the possible solution ??/

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

GilaMonster (9/12/2011)
How would you handle things like these:
John van Wyk (van Wyk is the surname)
David West Jnr (West is the surname)
etc...

... or

Gian Maria De Rossi ("Gian Maria" is the name, "De Rossi" is the surname)

Angela Maria Di Giovanni ("Angela Maria" is the name, "Di Giovanni" is the surname)

🙂

-- Gianluca Sartori

faruk.arshad SSC Veteran Points: 262 More actions · Answer 6

well tatz not important whether is the surname or not.. all i need is to break the string 🙂

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Ok: so just use the code in the article.

You can use that code to create the split function and then you can use the function to break the input string in pieces.

You will have it broken in rows, but you can easily turn it in columns using PIVOT or a crosstab.

-- Gianluca Sartori

faruk.arshad SSC Veteran Points: 262 More actions · Answer 8

Select

full_name, Case WHEN full_name LIKE '% % %'

Then

SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1),

CHARINDEX(' ', (SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1),

(LEN(full_name) -

CHARINDEX(' ', full_name))))))

Else

null

End

'Middle_Name' From #names

update

#names set middle_name=Case WHEN full_name LIKE '% % %'

Then

SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1),

CHARINDEX(' ', (SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1),

(LEN(full_name) -

CHARINDEX(' ', full_name))))))

Else

null

End

it can also done by this!! 🙂

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 9

Hi,

Another way (Note:- When you are sure that First , Middle and Last Name are mentioned in order)

DECLARE @STRING1 NVARCHAR(100)

DECLARE @STRING2 NVARCHAR(100)

DECLARE @STRING3 NVARCHAR(100)

DECLARE @F INT

DECLARE @m INT

SET @STRING1 = 'PALASH SQL GORAI'

SET @F = (SELECT CHARINDEX(' ',@STRING1))

SET @STRING2 = (SELECT LTRIM(SUBSTRING(@STRING1, @F,100)))

SELECT @m = (SELECT CHARINDEX(' ',LTRIM(SUBSTRING(@STRING1, @F,100))))

SET @STRING3 = (SELECT LTRIM(SUBSTRING(@STRING2, @m,100)))

SELECT SUBSTRING(@STRING1, 1, @F) AS FIRSTNAME, SUBSTRING(@STRING2, 1, @m) AS MIDDLENAME, @STRING3 AS LASTNAME

thanks

PG