December 17, 2009 at 9:59 pm
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
December 18, 2009 at 2:11 am
It is very diffcult to assume with the given data. Provide sample data.
December 18, 2009 at 2:33 am
I had provided the sample data only
December 18, 2009 at 2:39 am
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???
December 18, 2009 at 5:05 am
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
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
December 18, 2009 at 5:31 am
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.
December 18, 2009 at 5:44 am
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 datacommon 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.
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
December 18, 2009 at 6:28 am
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... 😉
December 18, 2009 at 6:55 am
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.
December 18, 2009 at 8:13 am
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.
December 18, 2009 at 11:35 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply