-
Need help with inSTR (Access) CharIndex (SQL)
hi, I wanna use the charindex function in the next query
"SELECT * FROM products where mid(productDescrip,1,100) like '%"&
descrip &"%'"
The 100 I need replace it with the initial position of a string like
this
"SELECT * FROM products where
mid(productDescrip,1,inSTR(1,productDescrip,">")) like '%"& descrip &"%'"
and I did this:
"SELECT * FROM products where mid(productDescrip,1,CharIndex('>',
productDescrip,1)) like '%"& descrip &"%'"
but didn't work.
Do you have any idea about?
Thanks a lot!
-
What is the outcome you need from this? It looks like you are trying to find "descrip" somewhere in a field named productDescrip. Once you find "descrip" what do you want to happen? Please don't discribe the coding, but what you want to happen within the productDescrip field.
The reason I ask this is because I could not figure out from your code what you want to do.
-
this is what I want
I have a memo field with a long text, but I need make a search only in the text before a mark like >, I don't want look up thru all the text, so I need the position for an especific character to cut the text and find only in the substring.
For instance, in this replay I wanna look for the word "memo", but only in the next substring:
"I have a memo field with a long text, but I need make a search only in the text before a mark like"
and if the string to find is "substring" there is no results because the word "substring" is not in the text "I have a memo field with a long text, but I need make a search only in the text before a mark like"
thank for your help, my language is not English, I apologize any error or misunderstanding.
-
I forgot say that the query with charindex works fine in SQL Server, but I am using access database on internet and I am sending my query from asp code in a Windows platform IIS
-
Do you need to "escape" the ">" when doing this w/ asp/html?
Example... in CGI/Perl if I am passing an email address I need to insert a \ before the @ - username\@domain.com
-
What means escape
hi, thanks for answer... but, I don't understand what do you mean with "escape", I only need catch the position of the first "<br>" in the whole text to can restraint the string in where I want look up.
-
I was wondering if the < is being seen as HTML code by the ASP instead of as part of your SQL query. It is just a thought... when passing certain characters in PERL/CGI scripts building HTML pages it is necessary to insert a "\" in front of the character so that it will not be misinterpreted. It is worth a shot.
-
I am not using Perl/CGI
hello again, I am still trying to do this, thanks to all for the tips, but I am using clasic asp, I only need identify the position where a substring is located, but in the clause "where" or my SQL query, I guess is imposible, I've been looking arround the web and nobody have any idea about whether is available ins Access or not. The charIndex works fine in the select area, but in the "where" conditions... well, I almost forgot the reserch... but... well, I know this is a challenge... jejeje, any idea, will be apreciated!
-
Could you post your code that works with SQL Server and the code you have so far with .asp? It would be a big help in helping you.
-
You said that this was working on SQL server?
"SELECT * FROM products where mid(productDescrip,1,CharIndex('>',
productDescrip,1)) like '%"& descrip &"%'"
Try this in the ASP code
"SELECT * FROM products where mid(productDescrip,1,CharIndex('\>',
productDescrip,1)) like '%"& descrip &"%'"
-
hello again
hi, in the begining (in my desperation) I made a mistake, now here is the code where you can prove what I want:
The next script you can copy and paste in a SQL Query Analyzer:
-- begin script
-- begin script
-- creating a table to the test
select 'Hello, this is my text where I want search. <br> And this is the rest of the text in where I do not want to search.' myText into tbl_test
-- this select has no charindex, instead has the value = 10
SELECT myText FROM tbl_test where substring(myText,1,10) like '%' + 'hello' + '%'
-- this select exec the query that I am looking for, notice the
-- charindex function instead the 10 and give the right result
SELECT myText FROM tbl_test where substring(myText,1, charindex('<br>',myText,1) ) like '%' + 'hello' + '%'
-- this select give me the right result too, because 'rest' is
-- not in the text before <br> lable
SELECT myText FROM tbl_test where substring(myText,1, charindex('<br>',myText,1) ) like '%' + 'rest' + '%'
-- end script
thanks for helping me!
-
this is the error when I try to run the query in asp environment:
Microsoft JET Database Engine error '80040e14'
Undefined function 'charindex' in expression.
-
and this is my asp sql parameter to exec
strVar = "hello"
SQL = "SELECT myText FROM tbl_test where mid(myText,1,charindex('<br>',myText,1)) like '%"& strVar &"%'"
-
KLMCats, thanks I tried with the slash but wasn't work.
-
galamero,
This string will work at the SQL, but it has to be created in the .asp code before being used as the SQL. You can not really pass parameters to SQL in Access or .asp. But, within the .asp code, you can create the '*Hello*' and append it to the end of the rest of the SQL string.
Code:
SELECT myText FROM tbl_test where mid(myText,1,instr(MyText, '<br>')) like '*Hello*'
Vic
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|