469,128 Members | 1,397 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

PROBLEM using CharIndex(), IF, OR etc- PLEASE HELP

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

May 10 '07 #1
2 2808
SB
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) & "%' "

May 11 '07 #2
SB
On May 11, 11:02 am, SB <othell...@yahoo.comwrote:
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.

May 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by M Wells | last post: by
5 posts views Thread by Willem | last post: by
3 posts views Thread by csomberg | last post: by
3 posts views Thread by chudson007 | last post: by
1 post views Thread by chudson007 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.