Transact SQL challenge - Trim data after a special character

Scudboy
Scudboy used Ask the Experts™
on
I am trying to return a string up to a certain character, and cannot figure out what I should be using.  This may be a newbie question...
Basically, a varchar(255) column named COMMENT has the following value:
  'Working on a solution to get this to appear_NOT THIS'

When I select that column I would like the result to be
  'Working on a solution to get this to appear'

So effectively trimming everything to the right of the _ (underscore).

Is that even possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I should clarify, the values held in this column will not be have a consistant number of leading characters - so the values would/could be:

COMMENTCOLUMN
I would like this to appear_NOT THIS
Working on a solution to get this to appear_NOT THIS
This is what we'd want to show up_NOT THIS SECTION
This is good_this is bad and we do not want it to show up in the return list of values.
Commented:
Declare @NotThis nvarchar(100), @actual_Srting nvarchar(1000),@Result nvarchar(1000)

set @NotThis= '#NOTTHIS#'
SET @actual_Srting= 'I am working on SQL Express and SQL 2008#NOTTHIS#'

SET @Result=SUBSTRING(@actual_Srting, 1, CHARINDEX('#NOTTHIS#', @actual_Srting) - 1)

Print @Result
Chief Technology Officer
Most Valuable Expert 2011
Commented:
I agree with above; however, given you only want what is to the left, I would typically use LEFT(...).  Nothing wrong with SUBSTRING(...) though.  Additionally, ensure that you wrap this with CASE to account for non-existence of '_'; otherwise, you will get invalid syntax.

e.g.,
CASE CHARINDEX('_', COMMENT) WHEN 0 THEN COMMENT ELSE LEFT(COMMENT, CHARINDEX('_', COMMENT)-1) END

Open in new window


Please note the gist of the solution is what was provided already by dba2dba, so please credit accordingly.

Hope that helps!

Author

Commented:
Hi dba2dba -
The issue with using the statement above is that it assumes the removed text will be consistent.  This won't be the case.

In this example -
COMMENTCOLUMN
I would like this to appear_NOT THIS
Working on a solution to get this to appear_NOT THIS
This is what we'd want to show up_NOT THIS SECTION
This is good_this is bad and we do not want it to show up in the return list of value

Open in new window


We'd want to remove everything after the underscore (or # sign, or whatever would work in this capacity).

Author

Commented:
MWVisa1 nailed it - thanks much for the assistance, split points per request!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial