Field with 6 data points seperated by ~
in [Programming SQL]
|
From: RedGlow06 via SQLMonster.com on 19 Jan 2010 14:28 I have a other info field that for one client they pass 6 data points in. It is set up that they always have the same 6 data points. I need to seperate the 6 datapoints into seperate fields. The datapoints can have different amounts of text and numbers so a simple mid statement won't work. Example ex1 Price:$10.99~Offer:2 for 1~Tax:$8.79~Shipping~$2.99~Rebate:$5. 00~Commisionable Amt:$5.99 ex2 Price:$1500.00~Offer:limited time tv special 30 days no interest~Tax:$240. 00~Shipping~$0.00~Rebate:$500.00~Commisionable Amt:$1100.00 Any ideas or help is greatly appreciated -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
From: RedGlow06 via SQLMonster.com on 19 Jan 2010 14:41 Fixed my example: ex1 Price:$10.99~Offer:2 for 1~Tax:$8.79~Shipping:$2.99~Rebate:$5. 00~Commisionable Amt:$5.99 ex2 Price:$1500.00~Offer:limited time tv special 30 days no interest~Tax:$240. 00~Shipping:$0.00~Rebate:$500.00~Commisionable Amt:$1100.00 -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
From: Tom Moreau on 19 Jan 2010 14:49 This can be handled with a CLR function that takes a delimited string, parses it and returns a table. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "RedGlow06 via SQLMonster.com" <u57010(a)uwe> wrote in message news:a25b7d1c0c40f(a)uwe... I have a other info field that for one client they pass 6 data points in. It is set up that they always have the same 6 data points. I need to seperate the 6 datapoints into seperate fields. The datapoints can have different amounts of text and numbers so a simple mid statement won't work. Example ex1 Price:$10.99~Offer:2 for 1~Tax:$8.79~Shipping~$2.99~Rebate:$5. 00~Commisionable Amt:$5.99 ex2 Price:$1500.00~Offer:limited time tv special 30 days no interest~Tax:$240. 00~Shipping~$0.00~Rebate:$500.00~Commisionable Amt:$1100.00 Any ideas or help is greatly appreciated -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
From: Plamen Ratchev on 19 Jan 2010 16:59 Take a look at Erland Sommarskog's articles on the topic: http://www.sommarskog.se/arrays-in-sql.html -- Plamen Ratchev http://www.SQLStudio.com
From: --CELKO-- on 19 Jan 2010 23:23 >> I have a other info field that for one client they pass 6 data points in.. It is set up that they always have the same 6 data points. I need to separate the 6 data points into separate fields. << Columns are not fields; you need to know the differences to ever write good SQL. The correct answer is to do this in the front end and pass scalar values with a stored procedure from the host program.
|
Pages: 1 Prev: Need help with "SUM-IF" query Next: Help with date range. |