use of Replace option in the inquiry shown

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

General SQL Server Forums

New to SQL Server Programming

use of Replace option in the inquiry shown

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 01:29:28

In the inquiry below :
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingCity,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + ProductName FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') as ProductName,
STUFF((SELECT ',' + CAST(d.Options AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179

Where would the REPLACE function go and syntax to change string of data in the Options field . I do not know where to place the REPLACE function .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 02:18:45

it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159154


STUFF((SELECT ',' + CAST(replace(d.Options, '</i>', '') AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS Options

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 02:33:07

quote:
Originally posted by khtan

it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159154

STUFF((SELECT ',' + CAST(replace(d.Options, '', '') AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS Options

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

Ok - I have the inquiry looks like :
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingCity,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + ProductName FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') as ProductName,
STUFF((SELECT ',' + CAST(replace(d.Options, '', '') AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options

FROM Orders o

where o.OrderID > 1179

Now I get error
Your SQL is invalid: Argument data type text is invalid for argument 1 of replace function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 02:35:33

Option is text ?
change to


replace( CAST(d.Options AS VARCHAR(100)), '</i>', '')

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 02:42:09

quote:
Originally posted by khtan

Option is text ?
change to

replace( CAST(d.Options AS VARCHAR(100)), '', '')

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

Is this correct
STUFF((SELECT ',' + CAST replace( CAST(d.Options AS VARCHAR(100)), '', '')AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
Now I get Your SQL is invalid: Incorrect syntax near '('.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 03:12:51

[code]
STUFF((SELECT ',' + ~~CAST~~ replace( CAST(d.Options AS VARCHAR(100)), '', '') ~~AS VARCHAR(100))~~ FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
[/code]

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 10:52:08

quote:

Originally posted by khtan


STUFF((SELECT ',' + CAST replace( CAST(d.Options AS VARCHAR(100)), '</i>', '') AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options

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

That works fantastic ! Thanks for your great work : )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources