Parsing data delimited by comma

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parsing data delimited by comma

Author

Topic

jeeve01
Starting Member

25 Posts

Posted - 2009-07-18 : 05:11:10

How to parse this data Data

30%,20%,10% and so on

where percent is equal to a,b,c and so on respectively

to fit into a formula

(Price*(1-a)*(1-b)*(1-c)...)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 05:12:32

make use of fnParseString

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 05:22:42

[code]
declare @data varchar(20)

select @data = '30%,20%,10%'

select (1 - convert(decimal(10,2), replace(dbo.fnParseString(-1, ',', @data), '%', ''))/100.0) *
(1 - convert(decimal(10,2), replace(dbo.fnParseString(-2, ',', @data), '%', ''))/100.0) *
(1 - convert(decimal(10,2), replace(dbo.fnParseString(-3, ',', @data), '%', ''))/100.0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 05:25:54


declare @data   varchar(20)
declare	@disc	decimal(20,4)

select	@data = '30%,20%,10%'

select	@disc	= isnull(@disc, 1) * (1 - Data / 100.0)
from	dbo.fnParseList(',', replace(@data, '%', ''))
order by RowID

select	@disc

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-18 : 05:56:46

it seems that my system has no fnParseString Function nor fnParselist

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 06:05:59

It's a UDF. click on the link in my posts. You can get it there.

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-20 : 21:15:50

cant invoke the fnParseString function, say @data is coming from table A column DiscountWord or A.Discountword. Was @Section = @data? Pls show me the structure.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 21:18:57

can you show us the query ?

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-20 : 21:28:25

select
docno,
arcode,
(select a.name1 from bcar a where a.code = c.arcode) as [customer name],
docdate,
itemcode,
(select b.name1 from bcitem b where b.code = c.itemcode) as [product name],
qty,
shelfcode,
(select (1 - convert(decimal(10,2), replace(dbo.fnParseString(-1, ',', @data), '%', ''))/100.0) *
(1 - convert(decimal(10,2), replace(dbo.fnParseString(-2, ',', @data), '%', ''))/100.0) *
(1 - convert(decimal(10,2), replace(dbo.fnParseString(-3, ',', @data), '%', ''))/100.0)),
amount
from c
where docdate >='2009/01/01' and docdate <= '2009/12/31'
order by docno

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 21:32:01

replace @data with the actual column in your table

The column that contains '30%,20%,10% '

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-20 : 21:32:37

here, source of discountword is table c which is @data

jeeve01
Starting Member

25 Posts

Posted - 2009-07-20 : 21:40:39

Invalid object name 'dbo.fnParseString'.:)how bout fnParseString function where should i place this script. this is the fist time i use the user defined function. should i load/create this to string function folder.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 21:47:38

for fnParseString, It is a function the extract the required string out of a delimited string. You can get it from the link i provided. Just click on it or here

fnParseString : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033#315325

fnParseList : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033#315323

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-20 : 23:55:09

i got a null discount result:) using fnParseString

jeeve01
Starting Member

25 Posts

Posted - 2009-07-21 : 00:03:43

especially for the rows with only one discount, say 30% or 30%,10%

jeeve01
Starting Member

25 Posts

Posted - 2009-07-21 : 00:09:30

but fnParseString are perfect for items with 30%,10%,5% or three discount level, and fnParseString generate return after i edit @text text to @Text varchar().

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 00:16:05

use ISNULL() to convert it to 0


select	
	c.docno,
	c.arcode,
	a.name1 	as [customer name],
	c.docdate,
	c.itemcode,
	b.name1 	as [product name],
	c.qty,
	c.shelfcode,
	(1 - isnull(convert(decimal(10,2), replace(dbo.fnParseString(-1, ',', @data), '%', '')), 0) / 100.0) *
	(1 - isnull(convert(decimal(10,2), replace(dbo.fnParseString(-2, ',', @data), '%', '')), 0) / 100.0) *
	(1 - isnull(convert(decimal(10,2), replace(dbo.fnParseString(-3, ',', @data), '%', '')), 0) / 100.0)	as [discount],
	c.amount
from 	c
	inner join bcar a	on	c.arcode	= a.code
	inner join bcitem b	on	c.itemcode	= b.code
where 	docdate >='2009/01/01' 
and 	docdate <= '2009/12/31'
order by docno

i also converted your sub-query code to use INNER JOIN

KH
[spoiler]Time is always against us[/spoiler]

jeeve01
Starting Member

25 Posts

Posted - 2009-07-21 : 03:52:54

very great master. :). will study this deeper..... a thousand thanks sir kh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources