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
2  2986 
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.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Robin Tucker |
last post by:
My code prints out 0 for the value of charindex ( 'Hello.', '.' ).
Did I miss something? I think it should print out 6!
|
by: M Wells |
last post by:
Hi All,
Just wondering if it's any more efficient to use the following SQL
statement to return a partial match from a column as opposed to the
second statement.
SELECT * FROM Table1 WHERE...
|
by: sdowney717 |
last post by:
Using the the NumId from TitleData, I would like to delete the
corresponding row in Bookdata using pure SQL. I want it to delete all
rows in bookdata where the Titledata.NumID is a match to...
|
by: Willem |
last post by:
Hello
I am quite hopeless and of course a newbe.
The situation: Sql2k / query
I would like it ot break down the following string:
2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094
|
by: csomberg |
last post by:
SQL Server 2000
Ya know, it is always the simplest stuff that gets ya !!
I am having the hardest time getting a simple piece of code working.
Must be brain dead today.
Goal: Get the users...
|
by: chudson007 |
last post by:
With the syntax below, why is field1a not "A" if field1 does not
contain "_"
SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
field1)) ELSE 'A' END, field1
FROM
|
by: youngster94 |
last post by:
Hey all,
I've written a VB.Net app that creates picture badges complete with
barcodes. The problem is that the barcode quality is not good enough
to be read by scanners. I'm using the...
|
by: chudson007 |
last post by:
I have a field containing a string with '/' in it multiple times.
How can I return the charindex of the last occurance of '/' in the
string?
Regards,
Ciarán
|
by: db55 |
last post by:
This script doesn't work. Why?
UPDATE
SET = LTRIM(SUBSTRING(, 1,
convert(bigint, CHARINDEX(',', Comments)-1)))
WHERE NOT( IS NULL) AND LEN() > 8
Basically, I'm trying to...
|
by: anonieko |
last post by:
This approach I found very efficient and FAST when compared to the
rowcount, or Subquery Approaches.
This is before the advent of a ranking function from DB such as
ROW_NUMBER() in SQL Server...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |
