Function to split a string into 5 pieces

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Function to split a string into 5 pieces

Author

Topic

ratheeshsql
Starting Member

17 Posts

Posted - 2006-05-23 : 03:01:56

Hi,

I have an MS Excel file in which the name field contains a string value with 5 segments. I want to split it into 5 pieces as Lastname, first name,middle name, second middle name and namesuffix. The conventional 'substring' method is not applicable since the segments are of different lengths. I want to split it using some function. I can replace the segments within the string with '.' or any other characters using 'replace' function. Could any one help me in this matter?

Ratheesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 04:24:30

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-05-23 : 06:31:17

Well, if you just want to extract the 5 segments then this should do the trick - but its a bit of a beast!

SELECT
[COLUMN_1] = substring(MyCol + ',', 0 + 1, charindex(',', MyCol + ',', 0 + 1) - 0 - 1 )
, [COLUMN_2] = substring(MyCol + ',', charindex(',', MyCol + ',') + 1, charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) - charindex(',', MyCol + ',') - 1 )
, [COLUMN_3] = substring(MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1, charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) - charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) - 1 )
, [COLUMN_4] = substring(MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) + 1, charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) + 1) - charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) - 1 )
, [COLUMN_5] = substring(MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) + 1) + 1, charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) + 1) + 1) - charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',', charindex(',', MyCol + ',') + 1) + 1) + 1) - 1 )
FROM dbo.MyTable

Alternatively, if you want to do it with an UPDATE after the import then there is this:


DECLARE	@I1 int, @I2 int, @I3 int, @I4 int, @I5 int
UPDATE U
SET
	  @I1 = CHARINDEX(',', MyCol + ',')
	, [COLUMN_1] = LEFT(MyCol, @I1-1)
	, @I2 =  NullIf(CHARINDEX(',', MyCol + ',', @I1+1), 0)
	, [COLUMN_2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
	, @I3 =  NullIf(CHARINDEX(',', MyCol + ',', @I2+1), 0)
	, [COLUMN_3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
	, @I4 =  NullIf(CHARINDEX(',', MyCol + ',', @I3+1), 0)
	, [COLUMN_4] = SUBSTRING(MyCol, @I3+1, @I4-@I3-1)
	, @I5 =  NullIf(CHARINDEX(',', MyCol + ',', @I4+1), 0)
	, [COLUMN_5] = SUBSTRING(MyCol, @I4+1, @I5-@I4-1)
FROM	dbo.MyTable AS U

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-23 : 08:04:00

This will parse virtually any name string and return it in virtually any format. You can use it to return single portions of the name:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources