SSIS Substring like but not based on a position

Jason Yousef
Jason Yousef used Ask the Experts™
on
Hello all,
I didn't really know what to call what I want, so I named it SUBSTRING LIKE :)

basically I've these data on the pipe:

KEdfgAV,GAYdRI R$50 NEWARK AVE$BELLEVILLE NJ 07109
KOdfgALI,PAdgMAJA R$7  ORANGE AVE$S.ORANGE NY 05679
PdfgEL, SAdfgABH C.MD$17 EDISON  PLACE$NEWARK, MA 045605
GITgfNS-WIerAMS, LISA N.$97 BERGEN ST.$NEWARK, NJ 045603
OfTIZ,THgdfMAS R$465 MT. PROSPECT AVE$NEWARK, IL 05614
PECK, RICHARD E.$76 ND AVE$W. ORANGE NY 04552
ALreNSO, CeOS R$94 BLELD AVE$GLEN RIDGE, NW 045628
DerABNEH,NArR$969 ERT ST$RED BANK, IL 65461

and I need to keep only anything before the "$" sign.

how can that be done in SSIS derived column or through a script ?

any help is appreciated..
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In SQL Charindex will return the occurance of one string in another.

ie:  select charindex('$','Test$blahblah') returns 5
so you can use substring to cut it off
ie:  select substring('Test$blahblah', 1, charindex('$','Test$blahblah')-1)
in SSIS there's no Charindex, so you create your derived column like this
substring(yourcolumn, 1, FINDSTRING(yourcolumn + "$", "$", 1)-1)
 
In a SQL query do something like this
select left(yourcolumn, charindex('$', yourcolumn + '$')-1) from yourtable
Jason YousefSr. BI  Developer

Author

Commented:
God bless you for sharing the knowledge :) Thanks for helping the newbies.

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