• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

SQL Server 2005 parse string into two column table

In VB I am building a string which I am passing into my stored procedure as a varchar(MAX) variable

The string looks like this

Larry,100|jane,125|Bill,75

I want to parse this into
col1      col2
Larry     100
Jane       125
Bil           75
0
lrbrister
Asked:
lrbrister
  • 2
  • 2
  • 2
1 Solution
 
Ioannis ParaskevopoulosCommented:
Hi,

You would have to use an sql cursor. Are you inserting the results in a table?

Giannis
0
 
lrbristerAuthor Commented:
You joggled my memory with the "Table" comment

I remember getting some code from angelll years ago.

SELECT      MAX(CASE WHEN row_num % 2 = 1 THEN Value END) hsid,
        MAX(CASE WHEN row_num % 2 = 0 THEN Value END) hours
FROM      dbo.ParmsToList(@list, ',') d
GROUP BY(row_num - 1)/ 2
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using the function dbo.ParmsToList, code being here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

you can do it without a "cursor":
select left( f.value, charindex(',', f.value) - 1) col1
, substring( f.value, charindex(',', f.value) + 1, len(f.value))  col2 
  from dbo.ParmsTolist('Larry,100|jane,125|Bill,75', '|') f 

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
too slow :)
0
 
lrbristerAuthor Commented:
angelll,
I have received some great help from you for the past 10 years or so.

Usually, I just need to take a moment and look through what you gave me in the past already
0
 
Ioannis ParaskevopoulosCommented:
Hi,

If you want (i don't know if you can) to reallocate the points, it is fine by me. After all i really didn't think that was of much help. I really expected to just start a conversation and then give you an answer.

:)

Thanks anyways,
Giannis
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now