Results 1 to 12 of 12

Thread: Charindex

  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Post Unanswered: Charindex

    How can I get the value of charindex from the right side(not from the first) means from the last?

    Subhasih

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Charindex

    Could you give an example please?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    DECLARE @Expr1 varchar(15)
    SELECT @Expr1 = 'Way'
    SELECT CASE WHEN CHARINDEX(@Expr1, ShipAddress, 1) <> 0 
    	    THEN CHARINDEX(@Expr1, ShipAddress, 1) + LEN(@Expr1)
    	    ELSE 0
           END
      FROM Orders
     WHERE ShipAddress LIKE '%'+ @Expr1 + '%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

    blindman
    How does that work?

    Code:
    USE Northwind
    GO
    DECLARE @Expr1 varchar(15)
    SELECT @Expr1 = 'Way'
    
    
    SELECT CHARINDEX(@Expr1, REVERSE(ShipAddress)) 
      FROM Orders 
     WHERE ShipAddress LIKE '%'+ @Expr1 + '%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "How can I get the value of charindex from the right side(not from the first) means from the last?":

    declare @YourTextString varchar(100)
    declare @YourSearchCharacter char(1)
    set @YourTextString = 'For good database advice, visit DBFORUMS.COM.'
    set @YourSearchCharacter = 'V'

    select charindex(@YourSearchCharacter, REVERSE(@YourTextString)) as ReverseCharIndex

    ReverseCharIndex
    ----------------
    19

    I don't think subhasishray is trying to use the LIKE operator here, or trying to find the positions of substring greater than 1 character in length. A similiar statement could be developed for multi-character searches, though.

    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't the statement:

    How can I get the value of charindex from the right side(not from the first)
    Infer multiple search chars?

    The LIKE is just to minimize the sample result rows (excluses all the zeroes)

    But yeah, how about (and I found a 1 byte offset bug in my original):

    Code:
    USE Northwind
    GO
    DECLARE @Expr1 varchar(15)
    SELECT @Expr1 = 'Way'
    
    
    SELECT    LEN(ShipAddress)-CHARINDEX(REVERSE(@Expr1), REVERSE(ShipAddress))+1
    	, CHARINDEX(@Expr1, ShipAddress, 1) + LEN(@Expr1) - 1
    	, ShipAddress
      FROM Orders 
     WHERE ShipAddress LIKE '%'+ @Expr1 + '%'
    EDIT: Blindman, how would you do it differently?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oops. Correct Kaiser. I spaced the fact that Charindex can be used to search for string of more than one character.

    I like this method:

    declare @YourTextString varchar(100)
    declare @YourSearchString varchar(50)
    set @YourTextString = 'For good database advice, visit DBFORUMS.COM.'
    set @YourSearchString = 'vis'

    select charindex(REVERSE(@YourSearchString), REVERSE(@YourTextString)) + len(@YourSearchString)-1 as ReverseCharIndex

    ReverseCharIndex
    ----------------
    19

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ... and that fails if the search string is not found.

    This is more robust, returning 0 if the search string is not found, just like the regular CHARINDEX function:

    declare @YourTextString varchar(100)
    declare @YourSearchString varchar(50)
    set @YourTextString = 'For good database advice, visit DBFORUMS.COM.'
    set @YourSearchString = 'Dummy'

    select isnull(nullif(charindex(REVERSE(@YourSearchString) , REVERSE(@YourTextString)), 0) + len(@YourSearchString)-1, 0) as ReverseCharIndex

    blindman

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Would make a handy stock function:


    create function ReverseCharIndex(@SearchString varchar(500), @TextString varchar(500))
    returns int
    as
    begin
    return isnull(nullif(charindex(REVERSE(@SearchString), REVERSE(@TextString)), 0) + len(@SearchString)-1, 0)
    end

    blindman

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Looks oddly familiar...

    Getting cold in OooooHIo?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just rainy so far.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •