Get rid of some unwanted data - SQL Server Forums

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

Get rid of some unwanted data

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:13:34

I have inquiry that was developed with the help here . it works great
It is :
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

However in the field Options on our data base have some control characters so options looks like for example :
[Handlebars:Riser + Oury Grips][Size:55][Add Tire Set:No Extra Tire Set][Pedal Type:Standard
we want it to look like
[Handlebars:Riser + Oury Grips][Size:55][Add Tire Set:No Extra Tire Set][Pedal Type:Standard

How can we get rid of < and /i> in our data when we are reading the table

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:19:31

this is how data looks
[Handlebars:Riser + Oury Grips][Size:55][Add Tire Set:No Extra Tire Set][Pedal Type:Standard,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 00:25:09

use replace() function to remove it

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:26:18

quote:
Originally posted by khtan

use replace() function to remove it

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

Thanks - But I am novice can you show where that replace goes ?

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:36:46

quote:
Originally posted by khtan

use replace() function to remove it

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

Can you assist and show how is doen - i just want the output to be replaced in the report not actual data .Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 00:42:22

see the link that in my last post for the details.

basically it is something like


replace ( column_name , '</i>', '')

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:51:04

quote:
Originally posted by khtan

see the link that in my last post for the details.

basically it is something like

replace ( column_name , '', '')

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

Thanks i got it - but where do you place it in the inquiry - no matter where I place it I get syntax error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 02:15:53

"no matter where I place it I get syntax error"
please show us your query

continue here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159158
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159157

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

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 02:23:34

quote:
Originally posted by khtan

"no matter where I place it I get syntax error"
please show us your query

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

see below. this where I put the Replace function - I know is wrong - but do not know where it goes
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
Replace "d.options,'', '')
FROM Orders o
where o.OrderID > 1179

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-07 : 05:41:24

see below. this where I put the Replace function - I know is wrong - but do not know where it goes
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

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources