By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,929 Members | 1,447 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,929 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.