Split text data type into rows

Question

Split text data type into rows

snsingh

Right there with Babe

Points: 793
More actions
July 11, 2012 at 8:19 am

#261091

Hi All,
I have a text type column in which I have value like
ABC;NoDisplayName;Ignite Techn;NoDisplayVersion Xyz;Infy;Ignite Techn;1.2.3 MNP;TCS;AT&T;NoDisplayVersion.........................................
Now I need to split this text into rows. like in above example I have three rows and four columns. the columns are semicolumn delimeter(;) but there is no row delimter( actully it has but that is space and any column can have space in it). so the avoce text should looks like
ABC;NoDisplayName;Ignite Techn;NoDisplayVersion
Xyz;Infy;Ignite Techn;1.2.3
MNP;TCS;AT&T;NoDisplayVersion
please help me out.
Thanks
Sneh

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I suggest you read this article by Jeff Moden, http://www.sqlservercentral.com/articles/62867/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Problem I see with your sample data is that ther is no clear seperator between the values. Some of your beaks appear to be the semicolon (;) but others appear to be on a space ( ).

snsingh Right there with Babe Points: 793 More actions · Answer 3

seperator for column are good semicolumn(;). I also have row seperator i.e. space. but you know in columns we can have space so we can't say this particular space is row seperator.

Thanks

Sneh