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»»

Split a String Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 8:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
Hello Everyone
I am working on some old data that should not be store this way, but it is.

My data is two values with pipe separators.
10.0||14.5
or
2||34
or
7.1||19
or
4||11.7

I need to query this column and get each value separately. I have no idea how to go about this. There are always double pipe in the middle. But as you can see, there may or may not be decimal values in each. I think that is what is throwing me off. When perhaps, it really may not matter about the actual values.

I appreciate any and all assistance, suggestions and comments

Andrew SQLDBA
Post #1429875
Posted Tuesday, March 12, 2013 9:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
http://www.sqlservercentral.com/articles/Tally+Table/72993/
by Jeff Moden


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429877
Posted Tuesday, March 12, 2013 9:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
I was actually looking for something else. That really did not help.

Andrew SQLDBA
Post #1429905
Posted Tuesday, March 12, 2013 9:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
AndrewSQLDBA (3/12/2013)
I was actually looking for something else. That really did not help.

Andrew SQLDBA


What's the problem with it, Andrew? There is an alternative; I'm just curious.


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429906
Posted Tuesday, March 12, 2013 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
AndrewSQLDBA (3/12/2013)
I was actually looking for something else. That really did not help.

Andrew SQLDBA


Ok, then could you please provide a bit more details about what you have and expected you really want. From the way you have described your problem in the first post it did look like you were after common string split function.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429910
Posted Tuesday, March 12, 2013 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Let me one guess (just a specualtion). Do you want values split to columns? If so, just use the udf like that:

SELECT *
INTO #test
FROM (VALUES ('10.0||14.5'), ('2||34'), ('7.1||19'), ('4||11.7') ) v(val)
;

SELECT val, v1.Item, v2.Item
FROM #test
CROSS APPLY (SELECT Item FROM dbo.DelimitedSplit8k(val,'|') WHERE ItemNumber = 1) v1
CROSS APPLY (SELECT Item FROM dbo.DelimitedSplit8k(val,'|') WHERE ItemNumber = 3) v2



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429915
Posted Tuesday, March 12, 2013 10:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
For one thing, I do not have a Tally table setup on the server in which I am working.

So, are there any other ways of doing this without a tally table. There are only about 100,000 rows, and this is a one time update. So I do not really care about performance. What I am most concerned with is the amount of time to have this completed.

Yes, I can select the data into a temp table, in separate columns there. That would be fine. I can perform the update and then do what I need.

Thanks in advance
Andrew SQLDBA
Post #1429932
Posted Tuesday, March 12, 2013 10:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 23,009, Visits: 31,504
AndrewSQLDBA (3/12/2013)
For one thing, I do not have a Tally table setup on the server in which I am working.

So, are there any other ways of doing this without a tally table. There are only about 100,000 rows, and this is a one time update. So I do not really care about performance. What I am most concerned with is the amount of time to have this completed.

Yes, I can select the data into a temp table, in separate columns there. That would be fine. I can perform the update and then do what I need.

Thanks in advance
Andrew SQLDBA


Actually, you don't need a tally table to run the DelimitedSplit8K function. It uses a dynamic tally table built in the function.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429933
Posted Tuesday, March 12, 2013 10:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 1,780, Visits: 5,660
AndrewSQLDBA (3/12/2013)
Hello Everyone
I am working on some old data that should not be store this way, but it is.

My data is two values with pipe separators.
10.0||14.5
or
2||34
or
7.1||19
or
4||11.7

I need to query this column and get each value separately. I have no idea how to go about this. There are always double pipe in the middle. But as you can see, there may or may not be decimal values in each. I think that is what is throwing me off. When perhaps, it really may not matter about the actual values.

I appreciate any and all assistance, suggestions and comments

Andrew SQLDBA


For a one-off update, just use charindex

SELECT LEFT(value,CHARINDEX('||',value)-1) as Part1,STUFF(value,1,CHARINDEX('||',value)+1,'') as Part2



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1429955
    Posted Tuesday, March 12, 2013 11:24 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Thursday, July 17, 2014 3:19 PM
    Points: 975, Visits: 3,342
    Thank You Mister Magoo
    That worked perfectly

    Thanks for the suggestions everyone, just way overkill for what was actually needed.

    Andrew SQLDBA
    Post #1429994
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse