# Database Discussions > Microsoft Access >  Need help with inSTR (Access) CharIndex (SQL)

## galamero

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!

----------


## GolferGuy

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.

----------


## galamero

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.

----------


## galamero

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

----------


## KLMCats

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

----------


## galamero

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.

----------


## KLMCats

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.

----------


## galamero

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...  :Mad:   but... well, I know this is a challenge... jejeje, any idea, will be apreciated!

----------


## GolferGuy

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.

----------


## KLMCats

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 &"%'"

----------


## galamero

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!

----------


## galamero

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.

----------


## galamero

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 &"%'"

----------


## galamero

KLMCats, thanks I tried with the slash but wasn't work.

----------


## GolferGuy

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.


```
SELECT myText FROM tbl_test where mid(myText,1,instr(MyText, '<br>')) like '*Hello*'
```

Vic

----------


## galamero

hello, thank to all your replies, but I tried the next syntax and it works...

... mid(myText,1,inSTR(myText,chr(13))) like ...

I changed the <br> for chr(13) and it works... 

Thank you very much, I was reading all the replies and KLMCats post said something about add / so I was thinking the problem maybe was the <>, that was the hint.

Thanks again!
Galamero

----------

