Data to be split in different column

Question

Post reply

Data to be split in different column

sandy-833685

SSC Eights!

Points: 919
More actions
December 17, 2009 at 9:59 pm

#213815

Hi,
I am having data in single coumn like name address contact description i need segregate these data in different different coulmn like name address contact description etc
Sample1
XYZ
111,29th Main, ABCD - 10002
sample2
XYZ
111-29th Main- ABCD - 10002
sample3
111
29th Main
ABCD - 10002

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

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

It is very diffcult to assume with the given data. Provide sample data.

sandy-833685 SSC Eights! Points: 919 More actions · Answer 2

sandy-833685

SSC Eights!

Points: 919

December 18, 2009 at 2:33 am

#1093920

I had provided the sample data only

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 3

I mean real data and what is maximum rows and column u get??? and is there any delimiter??? and how can u arrive that it is first line in the address and last line in the address???

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

sandy-833685 (12/18/2009)
I had provided the sample data only

Sandy, please review and adjust the following sample code to match your sample data.

CREATE TABLE #Sample (Col1 CHAR(3), Col2 VARCHAR(60), Col3 VARCHAR(60))

INSERT INTO #Sample (Col1, Col2, Col3)

SELECT 'XYZ','111,29th Main, ', 'ABCD - 10002' UNION ALL

SELECT 'XYZ','111-29th Main- ', 'ABCD - 10002' UNION ALL

SELECT '111', '29th Main', 'ABCD - 10002'

SELECT * FROM #Sample

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sandy-833685 SSC Eights! Points: 919 More actions · Answer 5

Its a simple insert statement Its not possible for me to insert inverted commas for each statement, you can go through the sample data

common understanding is i am having data in excel file with coulmnA

there is the multiple information in one single cell like sandy,usa,12345678

and in other cell of same columnA sam,usa,789546,usa-12546 like this i am almost having 50 thousand cells from which i need to segregate the data in different coulmns based on the information provided in single cell.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sandy-833685 (12/18/2009)
Its a simple insert statement Its not possible for me to insert inverted commas for each statement, you can go through the sample data
common understanding is i am having data in excel file with coulmnA
there is the multiple information in one single cell like sandy,usa,12345678
and in other cell of same columnA sam,usa,789546,usa-12546 like this i am almost having 50 thousand cells from which i need to segregate the data in different coulmns based on the information provided in single cell.

I'm sorry but I have more interesting things to do than create your sample data for you. I sincerely hope that vyas and anybody else reading this thread subscribes to the same opinion.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 7

Sandy,

like Chris mentioned before, we'd like to see your input data as well as your expected results. The "sample" you provided is everything but clear.

Example:

Sample1

XYZ

111,29th Main, ABCD - 10002

What does that mean? Are those input data or expected results?

How XYZ and the next line are related to each other?

Regarding your Excel description: How are those cells linked to each other?

If those cells have no cross reference, then your Sample1 doesn't make sense.

Please keep in mind that we cannot look over your shoulder to see what you're trying to do. You need to help us help you.

It's like a bunch of people sitting on a box of powerful tools waiting for you to describe what to do with the picture you've scetched: throw it apart, stick it to the wall, digitalize it or whatever. Current status: sit and wait, expecting to move on... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sandy-833685 SSC Eights! Points: 919 More actions · Answer 8

Thx Lutz,

I will explain the complete scenario i am having table A with the

columns called Complaint No,call,Description, Contact no where

complaint no is auto generate

Call is user input column wherein user enter details regarding type of call

Description is user input column wherein user enter's the details of customer like customer name along with address and telephone..... whatever information is about customer is enter in this column some user use comma(,) separator and some use (;) or some wont use separator directly goes on typing.

Now i had got a project wherein i have to create the customer master which will be having the column called customer name ,customer add, customer telephone number etc... and i need to get these data from the table A column called description wherein they had feeded the information of customer name,address,telephone which i am finding it difficult to trace as there is no separator in description column of table A from where i can identify the name,address and telephone number there are almost 60000 such rows in table A from which i need to get the information and create the complete customer master.

LutzM SSC Guru Points: 107049 More actions · Answer 9

Would you mind sharing some sample data for column [description] representing some input scenarios (e.g. 10 to 20) together with expected results per row?

Do you have a guaranteed order of how the data are inserted within those cells?

The first step is to get the business rules on how to extract the data, since SQL Server has no magic wand to get any desired result. It's just a tool to apply your business rules to the data faster than doing it manually.

Example:

What would be the expected result for

John Benjamin Franklin 555 100 02?

Some possible results:

FirstName LastName Street Phone

NULL John Benjamin Franklin 555 100 02

John Benjamin Franklin 555 100 02

Benjamin John Franklin 555 100 02

Some business rules you might end up with:

If cell has x comma then [rule 1]

If cell has y semicolon then [rule 2]

Resulting first and last name must not have numbers.

Resulting phone number must have numbers only.

Based on such rules you probably would be able to semi automatically split the data.

The rest needs either to be converted to match one of the rules or would have to be entered manually.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Despite the lack of a result sample, it looks pretty clear that the split should occur on any characters (including Carriage Returns and LineFeeds) not in the range of [A-Z0-9] and ignore space around any such character.

Sandy... please take the time to read the article at the first link in my signature below... you'll get MUCH better answers that way even for simple stuff like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)