Findstring question

Question

Post reply

Findstring question

BlackGarlic

Ten Centuries

Points: 1091
More actions
March 12, 2008 at 2:30 pm

#128297

Hi guys. I am nwe to SSIS and studying SSIS, and while i was going over some books, I saw following express:
SUBSTRING(ProductName,1,(FINDSTRING(ProductName,"Size",1) - 2))
My understanding for this code is from product name field, from position 1, find a string starting with Size and remove them. But there is -2 after findstring. what is this -2 do?
Thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

It simply substracts 2 from the character position returned by the FINDSTRING.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anam Verma SSCertifiable Points: 5365 More actions · Answer 2

i think this would fail if it finds "Size" in the beginning of string. How will it substract 2 chars if current position of the string is 1?

I haven't tested that but seems logical from the statement.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 3

You are correct; a match found at the beginning of the string would result in a negative length for the outer SUBSTRING function, resulting in an error.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

srinath.chaitra SSC Veteran Points: 230 More actions · Answer 4

Hi All,

why I am not able to use FINDSTRING function in Script component

I am writng a below code

var1 = findstring(Row.PubDt, "-", 1)

This throws an error saying findstirng is not declared.

if I can;t use this function in script component what function I can use to find whether the string contains a character I am looking for.

Thanks in Advance

Regards

-Srinath

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

InStr([start,] string1, string2 [, compare])

Hope this helps,

Chad

sujatha.annadurai Newbie Points: 7 More actions · Answer 6

instr does not work in SSIS..What else can i use..I have similar issue where my findstring returns 0 and I have a -1 in my substring so the substring is failing for the rows that do not have the value I am looking for...How do I make this work with a case statement ?

ssexton Mr or Mrs. 500 Points: 574 More actions · Answer 7

Instr is the PL/SQL equivalent of Substring. In SSIS try the Substring function.

Hope that helps.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

ssexton (3/24/2009)
Instr is the PL/SQL equivalent of Substring. In SSIS try the Substring function.
Hope that helps.

Ummm..... no it's not... INSTR in PL/SQL is the equivalent of CHARINDEX in T-SQL. SUBSTR in PL/SQL is the equivalent of SUBSTRING in T-SQL except that SUBSTR in PL/SQL can count from the right as well as the left.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ssexton Mr or Mrs. 500 Points: 574 More actions · Answer 9

My apologies, you are correct. My response was too brief to be informative (as informative as yours). It was purely meant to provide an alternative for the Instr function in the previous example provided.

Since neither instr or charindex is an option with respect to helping him find the solution within SSIS, SUBSTRING was the alternative suggestion.

Hope that clarifies.