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: Plamen Ratchev on 6 Jul 2008 21:54 Steve, I agree on the performance note for SUBSTRING vs. CHARINDEX. Seems there is only need for a select statement, so WRITE will not apply. Plamen Ratchev http://www.SQLStudio.com
From: Viccu on 6 Jul 2008 22:16 I tried both the approaches and it did not work. Still having spaces after the Colon in the result. Thank you "Plamen Ratchev" wrote: > 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: Plamen Ratchev on 6 Jul 2008 22:25 Could you please post INSERT statements with sample data to reproduce those results? Taking your table and sample data as posted: CREATE TABLE [dbo].[Orders] ( [OrdersId] [int] IDENTITY(1,1)NOT NULL, [Comments] [varchar](max) NOT NULL ); INSERT INTO Orders VALUES('01/02/01: My text with a leading space'); INSERT INTO Orders VALUES('01/02/01:My text with no leading space.'); Both queries below: SELECT CASE WHEN CHARINDEX(': ', comments) = 9 THEN STUFF(comments, 10, 1, '') ELSE comments END AS comments FROM Orders; SELECT CASE WHEN SUBSTRING(comments, 9, 2) LIKE ': ' THEN STUFF(comments, 10, 1, '') ELSE comments END AS comments FROM Orders; Return the same result set which does not have a space: comments ---------------------------------------- 01/02/01:My text with a leading space 01/02/01:My text with no leading space. HTH, Plamen Ratchev http://www.SQLStudio.com
From: Viccu on 6 Jul 2008 22:54 I tried the below and it appears to be working Ok, is it not good.? I can still post the queries. Please advise. SELECT CASE WHEN CHARINDEX(': ', O.comments) = 9 THEN SUBSTRING(O.Comments,0,10) + RTRIM(LTRIM(SUBSTRING(O.Comments,11,LEN(O.Comments)))) ELSE comments END AS comments FROM Orders O; "Plamen Ratchev" wrote: > Could you please post INSERT statements with sample data to reproduce those > results? > > Taking your table and sample data as posted: > > CREATE TABLE [dbo].[Orders] > ( > [OrdersId] [int] IDENTITY(1,1)NOT NULL, > [Comments] [varchar](max) NOT NULL > ); > > INSERT INTO Orders VALUES('01/02/01: My text with a leading space'); > INSERT INTO Orders VALUES('01/02/01:My text with no leading space.'); > > Both queries below: > > SELECT CASE WHEN CHARINDEX(': ', comments) = 9 > THEN STUFF(comments, 10, 1, '') > ELSE comments > END AS comments > FROM Orders; > > SELECT CASE WHEN SUBSTRING(comments, 9, 2) LIKE ': ' > THEN STUFF(comments, 10, 1, '') > ELSE comments > END AS comments > FROM Orders; > > Return the same result set which does not have a space: > > comments > ---------------------------------------- > 01/02/01:My text with a leading space > 01/02/01:My text with no leading space. > > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com >
From: Plamen Ratchev on 6 Jul 2008 23:15
If this works then you can use it. Maybe you have more than a single space after ':' and LTRIM helps to remove it. BTW, better to change: SUBSTRING(O.Comments, 0, 10) to: SUBSTRING(O.Comments, 1, 9) The start position in a string is 1 not 0. HTH, Plamen Ratchev http://www.SQLStudio.com |