Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

csv string Expand / Collapse
Author
Message
Posted Sunday, October 10, 2010 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
I have 2 csv string a follows..

String1= ‘a,b,c,d’
String2=’apple,boy,college,dog’

I have to insert the table as
-------------------
index | Description
-------------------
a | Apple |
b | Boy |
c | college |
--------------------

Any help to achieve this?.... Thanx in advance
Post #1001808
Posted Sunday, October 10, 2010 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
There are multiple "split string" tvf functions you can find at SSC. Just use its search feature.

Just add an identity column to the result table and join on that.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1001816
Posted Sunday, October 10, 2010 1:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,580, Visits: 8,856
Using the DelimitedSplit8K function, you would end up with:

DECLARE @String1 varchar(8000),
@String2 varchar(8000);

SET @String1 = 'a,b,c,d';
SET @String2 = 'apple,boy,college,dog';

SELECT a.Item, b.Item
FROM dbo.DelimitedSplit8K(@string1, ',') a
JOIN dbo.DelimitedSplit8K(@string2, ',') b
ON a.ItemNumber = b.ItemNumber;


Here is the latest version of the Delimited Split Function


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001839
Posted Sunday, October 10, 2010 5:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:06 PM
Points: 36,711, Visits: 31,160
WayneS (10/10/2010)
Using the DelimitedSplit8K function, you would end up with:

DECLARE @String1 varchar(8000),
@String2 varchar(8000);

SET @String1 = 'a,b,c,d';
SET @String2 = 'apple,boy,college,dog';

SELECT a.Item, b.Item
FROM dbo.DelimitedSplit8K(@string1, ',') a
JOIN dbo.DelimitedSplit8K(@string2, ',') b
ON a.ItemNumber = b.ItemNumber;


Here is the latest version of the Delimited Split Function


I guess I should add the latest performance enhancements to that and put it in the "Script Locker", huh?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1001855
Posted Sunday, October 10, 2010 5:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,580, Visits: 8,856
Jeff Moden (10/10/2010)
WayneS (10/10/2010)
Using the DelimitedSplit8K function, you would end up with:

DECLARE @String1 varchar(8000),
@String2 varchar(8000);

SET @String1 = 'a,b,c,d';
SET @String2 = 'apple,boy,college,dog';

SELECT a.Item, b.Item
FROM dbo.DelimitedSplit8K(@string1, ',') a
JOIN dbo.DelimitedSplit8K(@string2, ',') b
ON a.ItemNumber = b.ItemNumber;


Here is the latest version of the Delimited Split Function


I guess I should add the latest performance enhancements to that and put it in the "Script Locker", huh?


I thought this had the lastest...???

Can the code in the "Script Locker" be updated as it gets enhanced? How do we get access to it? (The scripts section here at SSC can't be updated... or at least I haven't figured out how to update the ones that I've put in there!)


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001860
Posted Sunday, October 10, 2010 6:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:06 PM
Points: 36,711, Visits: 31,160
Heh... there's another couple of enhancements to make that came up recently... some for performance (2 to be precise) and some for those bloody "space" delimiters. I'm just not done testing, yet.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1001866
Posted Monday, October 11, 2010 12:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
great!!!!!!! thank u jeff, wayne and ALZDBA for ur help....
Post #1001933
Posted Monday, October 11, 2010 12:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
Hi Jeff...

Could please tell me why you use following snipit in your split function?... whts it purpose exactly?


WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)
Post #1001936
Posted Monday, October 11, 2010 12:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
http://www.sqlservercentral.com/articles/T-SQL/62867/... i got ur article jeff
Post #1001942
Posted Monday, October 11, 2010 4:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:06 PM
Points: 36,711, Visits: 31,160
mail4sha (10/11/2010)
http://www.sqlservercentral.com/articles/T-SQL/62867/... i got ur article jeff


So you're all set then?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1002035
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse