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 21:16 I have written something like this. SELECT SUBSTRING(O.Comments,0,10) + RTRIM(LRIM(SUBSTRING(O.Comments,11,LEN(O.Comments)))) as Comments from Orders O.... And it seems to work Ok. But is this the best way? Please advise Viccu "Viccu" wrote: > 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
From: Viccu on 6 Jul 2008 21:23 But the issue is this field can have any values, if this has date and comments like '01/11/00: something', then the space should be ignored. How to I check the occurance of : in the value, then I can write the query, otherwise I can simply write a select Comments from Orders. Thank you in advance for any pointers. "Viccu" wrote: > I have written something like this. > SELECT SUBSTRING(O.Comments,0,10) + > RTRIM(LRIM(SUBSTRING(O.Comments,11,LEN(O.Comments)))) as Comments from Orders > O.... > And it seems to work Ok. But is this the best way? > > Please advise > > Viccu > > "Viccu" wrote: > > > 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
From: Plamen Ratchev on 6 Jul 2008 21:24 You can easily transform this to a select statement: SELECT DISTINCT CASE WHEN CHARINDEX(': ', comments) = 9 THEN STUFF(comments, 10, 1, '') ELSE comments END AS comments FROM Orders; HTH, Plamen Ratchev http://www.SQLStudio.com
From: Steve Kass on 6 Jul 2008 21:28 Plamen, Since CHARINDEX could require a scan of the entire [comments] string, in general, this will be more efficient: WHERE SUBSTRING(comments,9,2) = ': '; (Since ': ' = ':' is true, if there is the possibility of many colon-terminated 9-character strings, WHERE SUBSTRING(comments,9,2) LIKE ': ' might be better.) Also, Books Online suggests (but I have never verified) that for varchar(max), .WRITE might be the way to go (the WHERE clause will protect against calling ..WRITE on a NULL). The query plan does in fact show an extra Compute Scalar operator when STUFF is used, but not for .WRITE, so maybe... UPDATE Orders SET comments .WRITE ('', 9, 1) WHERE SUBSTRING(comments,9,2) LIKE ': '; Steve Kass Drew University http://www.stevekass.com 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
From: Plamen Ratchev on 6 Jul 2008 21:48
Try to see if the version that I posted works as you need it: SELECT DISTINCT CASE WHEN CHARINDEX(': ', comments) = 9 THEN STUFF(comments, 10, 1, '') ELSE comments END AS comments FROM Orders; Or as Steve suggested you can change the logic to use SUBSTRING: SELECT DISTINCT CASE WHEN SUBSTRING(comments, 9, 2) LIKE ': ' THEN STUFF(comments, 10, 1, '') ELSE comments END AS comments FROM Orders; HTH, Plamen Ratchev http://www.SQLStudio.com |