Need help!
in [Programming SQL]
|
Prev: How to construct check constraint to allow only one 'True' flag per vendor
Next: 'Invalid object name' for temp table when executing a SP
From: Viccu on 6 Jul 2008 20:06 Hi All I have a table, Orders table, that has a values for its comments field like e.g 01/02/01: My text with a leading space 01/02/01:My text with no leading space. I want to write a sql statement, that gets rid of the space after the :, so that it would look something like this, because I need only distinct values. 01/02/01:My text with a leading space. Here are the scripts for the table. CREATE TABLE [dbo].[Orders] ( [OrdersId] [int] IDENTITY(1,1)NOT NULL, [Comments] [varchar](max) NOT NULL ) Thank you in advance Viccu
From: Plamen Ratchev on 6 Jul 2008 20:23 Here is one way: UPDATE Orders SET comments = REPLACE(comments, ': ', ':') WHERE CHARINDEX(': ', comments) > 0; HTH, Plamen Ratchev http://www.SQLStudio.com
From: Plamen Ratchev on 6 Jul 2008 20:32 If you expect that ': ' may exist in the comments text in a different position and the date format at the beginning of the comments is fixed, you can be more precise: UPDATE Orders SET comments = REPLACE(comments, ': ', ':') WHERE CHARINDEX(': ', comments) = 9; HTH, Plamen Ratchev http://www.SQLStudio.com
From: Plamen Ratchev on 6 Jul 2008 20:35 Actually this is more accurate for fixed position cleanup: UPDATE Orders SET comments = STUFF(comments, 10, 1, '') WHERE CHARINDEX(': ', comments) = 9; HTH, Plamen Ratchev http://www.SQLStudio.com
From: Viccu on 6 Jul 2008 20:45
Thank you, but I need a Select Query on Distinct values and there could be data entered with space in future. "Plamen Ratchev" wrote: > Actually this is more accurate for fixed position cleanup: > > UPDATE Orders > SET comments = STUFF(comments, 10, 1, '') > WHERE CHARINDEX(': ', comments) = 9; > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com |