Spliting Column - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Spliting Column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-11 : 06:47:09

Ola writes "How do I split a Column into different column using delimiter
Spliting SMS text message divided by delimiter into different column in a table.
Example: Name * Address* Sex"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:00:09

Are there always same number of columns?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:27:57

If so, try this (with help of function here SELECT" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033)[code]SELECT LTRIM(RTRIM(dbo.fnParseString(3, '*', YourColumn))) AS Name,
LTRIM(RTRIM(dbo.fnParseString(2, '*', YourColumn))) AS Address,
LTRIM(RTRIM(dbo.fnParseString(1, '*', YourColumn))) AS Sex
FROM YourTable

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-11 : 07:57:27

If you want 4 or less columns, you can also use PARSENAME().

SELECT 
	PARSENAME(REPLACE(YourColumn,'*','.'),3) AS [Name],
	PARSENAME(REPLACE(YourColumn,'*','.'),2) AS Address,
	PARSENAME(REPLACE(YourColumn,'*','.'),1) AS Sex
FROM   YourTable

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:59:48

Unless some record is "Harsh Athalye Sr. * India * Male"

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-11 : 08:11:28

"Unless some record is "Harsh Athalye Sr. * India * Male""

declare @x varchar(50)

set @x = 'Harsh Athalye Sr. * India * Male'

SELECT 
	REPLACE(PARSENAME(REPLACE(REPLACE(@x,'.',char(21)),'*','.'),3),char(21),'.') AS [Name],
	REPLACE(PARSENAME(REPLACE(REPLACE(@x,'.',char(21)),'*','.'),2),char(21),'.') AS Address,
	REPLACE(PARSENAME(REPLACE(REPLACE(@x,'.',char(21)),'*','.'),1),char(21),'.') AS Sex

Of course unless Char(21) does not present in original data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-11 : 11:07:28

If you had the name and address of someone who wants sex, why would you want to separate them?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 15:03:57

Great one!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources