Charindex

How can I get the value of charindex from the right side(not from the first) means from the last?
SubhasihCould you give an example please?|||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 + '%'|||select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

blindman|||Originally posted by blindman
select charindex('[YourSearchCharacter]', REVERSE([YourTextString]))

blindman

How does that work?

USE Northwind
GO
DECLARE @.Expr1 varchar(15)
SELECT @.Expr1 = 'Way'

SELECT CHARINDEX(@.Expr1, REVERSE(ShipAddress))
FROM Orders
WHERE ShipAddress LIKE '%'+ @.Expr1 + '%'|||"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|||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):

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?|||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|||... 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|||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|||Looks oddly familiar...

Getting cold in OooooHIo?|||Just rainy so far.