What is the best way to essentially use the charindex(find) function
if the value is could be more than one variable (A or B or C)

I can't seem to get an "or", "if" or "select if" to work


Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
or 'LIB'

sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets(4).Range("j5") & "_VAR_" _
&
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "
_

Much appreciation if you can help

On May 11, 5:09 am, matthewwha...@gmail.com wrote:
> What is the best way to essentially use the charindex(find) function
> if the value is could be more than one variable (A or B or C)
>
> I can't seem to get an "or", "if" or "select if" to work
>
> Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
> or 'LIB'
>
> sqlstring = "SELECT Distinct substring([exposurename]," _
> & Len(Worksheets(4).Range("j5") & "_VAR_" _
> &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
> & ",charindex('GOV',[exposurename])-" &
> Len(Worksheets(4).Range("j5") _
> & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
> & ") AS Drivergrp2 " _
> & "FROM mars.dbo.mroInventoryProductGreeks_Latest
> " _
> & "Where producttype = 'creditdefaultswap' " _
> & "AND exposureName like '" &
> Worksheets(4).Range("j5") & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "
> _
>
> Much appreciation if you can help

Hi,
See if this works. You may have to play with double quotes to get it
working since I do not know VB or excel programming.

sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets(4).Range("j5") & "_VAR_" _
&
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",
isnull(nullif(isnull(nullif(charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ",0), charindex('FWD',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& "),0), charindex('LIB',[exposurename])-"
&
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ")
) AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "

On May 11, 11:02 am, SB <othell...@yahoo.com> wrote:
> On May 11, 5:09 am, matthewwha...@gmail.com wrote:
>
>
>
>
>
> > What is the best way to essentially use the charindex(find) function
> > if the value is could be more than one variable (A or B or C)
>
> > I can't seem to get an "or", "if" or "select if" to work
>
> > Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
> > or 'LIB'
>
> > sqlstring = "SELECT Distinct substring([exposurename]," _
> > & Len(Worksheets(4).Range("j5") & "_VAR_" _
> > &
> > Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
> > & ",charindex('GOV',[exposurename])-" &
> > Len(Worksheets(4).Range("j5") _
> > & "_VAR_" &
> > Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
> > & ") AS Drivergrp2 " _
> > & "FROM mars.dbo.mroInventoryProductGreeks_Latest
> > " _
> > & "Where producttype = 'creditdefaultswap' " _
> > & "AND exposureName like '" &
> > Worksheets(4).Range("j5") & "_VAR_" &
> > Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "
> > _
>
> > Much appreciation if you can help
>
> Hi,
> See if this works. You may have to play with double quotes to get it
> working since I do not know VB or excel programming.
>
> sqlstring = "SELECT Distinct substring([exposurename]," _
> & Len(Worksheets(4).Range("j5") & "_VAR_" _
> &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
> & ",
> isnull(nullif(isnull(nullif(charindex('GOV',[exposurename])-" &
> Len(Worksheets(4).Range("j5") _
> & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
> & ",0), charindex('FWD',[exposurename])-" &
> Len(Worksheets(4).Range("j5") _
> & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
> & "),0), charindex('LIB',[exposurename])-"
> &
> Len(Worksheets(4).Range("j5") _
> & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
> & ")
> ) AS Drivergrp2 " _
> & "FROM mars.dbo.mroInventoryProductGreeks_Latest
> " _
> & "Where producttype = 'creditdefaultswap' " _
> & "AND exposureName like '" &
> Worksheets(4).Range("j5") & "_VAR_" &
> Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "- Hide quoted text -
>
> - Show quoted text -

There is an elegant way to do this. However your excel ranges are too
big to accommodate that solution. Basically, you switch your string
positions in charindex function. Therefore, instead of looking for GOV
in target string, you take the whole target string and match with GOV.
It is something like:
Strip out gov etc: Substring(target_string,...)
Then you do: charindex('stripped string', 'GOV,FWD,LIB')
And if your target string is small (in your case probably it isn't)
then you can directly use it in charindex as: charindex('source
string', 'GOV,FWD,LIB')
Maybe in your excel you can create an extra column where you store the
stripped column then you can just match it with 'GOV,FWD,LIB'.
HTH.