FINDSTRING, SUBSTRING and REVERSE question

I'm trying to take a string and when I set up a derived column, to pass through all strings and remove any data that begins with a left parenthesis. 

I have tried these:

SUBSTRING([String Conversion].name,1, FINDSTRING([String Conversion].name, "(", 1)) - this reads the data, but does not strip / cleanse the data

I then tried this:

RIGHT([String Conversion].name, FINDSTRING(REVERSE([String Conversion.name), "(",1) -1) - I get an error that it cannot read the file to convert. 

I'm sure there are other methods - and possibly doing a Reverse of the Substring value would work?  I'm trying that to see what would happen.

Any additional ideas would be appreciated.

Thanks.

Are you trying to pass a blank string when the string begins with a left parenthesis, or simply remove the left parenthesis?

If you are simply trying to remove the left parenthesis this will work

FINDSTRING( YourInputString,"(",  1 )  ==1? RIGHT( YourInputString, LEN( YourInputString) -1 ):YourInputString

Thanks for your reply.

I'm trying to pass the column, and in any record, once there's a left parenthesis, then remove everything after the parenthesis.  I think I'm missing something in the expression, but it's close.

Ultimately, I will also have to replicate the job and in reverse order, pull every record that has the data within the parenthesis.

Ok so if your data is like this (abcdef want to return an empty column

If your data is like abc(def then you want to return abc in one column and def in another column

Or are you looking to do something like this.  Your data is abc(def)ghi and you want to return abcghi in one column and def in the other?

It's like this:  abc (def) g

What I want to do is eliminate anything after the parenthesis.  Resulting in abc only. 

A second job will need to consist of anything within the parenthesis (def), but that's for later.

Using your sample data abc(def)g

SUBSTRING( YourStringHere , 1, FINDSTRING( YourStringHere ,"(", 1 ) -1 ) returns abc

SUBSTRING( YourStringHere , FINDSTRING( YourStringHere, "(", 1 ) + 1  , FINDSTRING( YourStringHere,")", 1 ) -FINDSTRING( YourStringHere, "(", 1 ) -1 ) returns def

If this satisfies your question, please mark the question as answered.

I think this will work.  Now the issue I'm facing is when I run the package, I get this

"The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data".  Odd since I'm converting a name description field from DT_STR (200) to a DT_WSTR (50). 

Edit:  When I run the job now, my final result removes every string in the column that does not have the parenthesis involved in the value.  Would I add the following to the end of the string:

SUBSTRING( YourStringHere , 1, FINDSTRING( YourStringHere ,"(", 1 ) -1 ) : (YourStringHere)

You are pretty close.  Here is what I came up with:

FINDSTRING( YourStringHere ,"(", 1 ) > 0?SUBSTRING( YourStringHere  , 1, FINDSTRING( YourStringHere ,"(", 1 ) == 0?LEN( YourStringHere):   FINDSTRING( YourStringHere ,"(", 1 ) -1 ): YourStringHere

I am not sure how you would want the section to behave that pulls out the contents between the () if there are no (), return nothing?  If you want it to return nothing then I think this will work:

FINDSTRING( YourStringHere, "(", 1 )==0?"":SUBSTRING( YourStringHere  , FINDSTRING( YourStringHere, "(", 1 ) + 1  , FINDSTRING( YourStringHere,")", 1 ) - FINDSTRING( YourStringHere, "(", 1 )==0?0:FINDSTRING( YourStringHere, "(", 1 ) -1 )

I still show this data as a result:

"YourStringHere (" or if the row has no parenthesis in the name field it comes up empty. 

I appreciate your ideas...hopefully something triggers.

-- Change the @input and @anwser variables, where you put the answer you are looing for.

declare @input varchar(100), @answer varchar(100), @result varchar(100);

select @input = 'abcde(fg';
select @answer = 'abcde';

select @result = case when patindex('%(%',@input) = 0 then  @input else SUBSTRING(@input,1,patindex('%(%',@input) -1 ) end

select @result result,  case when @result = @answer then 'yippee' else 'oh no!' end note