Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Need help with inSTR (Access) CharIndex (SQL)

  1. #1
    Join Date
    Nov 2007
    Posts
    11

    Cool 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!

  2. #2
    Join Date
    May 2006
    Posts
    407
    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.

  3. #3
    Join Date
    Nov 2007
    Posts
    11

    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.

  4. #4
    Join Date
    Nov 2007
    Posts
    11
    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

  5. #5
    Join Date
    Oct 2007
    Posts
    11
    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

  6. #6
    Join Date
    Nov 2007
    Posts
    11

    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.

  7. #7
    Join Date
    Oct 2007
    Posts
    11
    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.

  8. #8
    Join Date
    Nov 2007
    Posts
    11

    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!

  9. #9
    Join Date
    May 2006
    Posts
    407
    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.

  10. #10
    Join Date
    Oct 2007
    Posts
    11
    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 &"%'"

  11. #11
    Join Date
    Nov 2007
    Posts
    11

    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!

  12. #12
    Join Date
    Nov 2007
    Posts
    11
    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.

  13. #13
    Join Date
    Nov 2007
    Posts
    11
    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 &"%'"

  14. #14
    Join Date
    Nov 2007
    Posts
    11
    KLMCats, thanks I tried with the slash but wasn't work.

  15. #15
    Join Date
    May 2006
    Posts
    407
    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
  •