What Is Best Way to Get Last Indexof Character in SQL 2008

What is best way to get last indexof character in SQL 2008

A little tricky, but you could do something like:

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[char]',REVERSE([field]))))

Is there a LastIndexOf in SQL Server?

If you want everything after the last _, then use:

select right(db_name(), charindex('_', reverse(db_name()) + '_') - 1)

If you want everything before, then use left():

select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_'))

Find index of last occurrence of a sub-string using T-SQL

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

Using LastIndexOf and SubString in mssql

SELECT Id,
       RIGHT(Code, CHARINDEX('.', REVERSE('.' + Code)) - 1) AS [Result],
       Code
FROM   YourTable 

Find last occurence of a character within a string

For those people who love monster 1-liners.

For MS2012 - Using IIF:

SELECT IIF(CHARINDEX('/', REVERSE(FieldA)) = 1,REVERSE(SUBSTRING(REVERSE(FieldA),2,CHARINDEX('/', SUBSTRING(REVERSE(FieldA), 2, LEN(FieldA))) - 1)),REVERSE(SUBSTRING(REVERSE(FieldA),1,CHARINDEX('/', REVERSE(FieldA)) - 1)))
FROM (
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA 
  UNION ALL
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' 
  UNION ALL   
  SELECT NULL
) TableA

For MS2008 Here is the same code in a CASE

SELECT A.FieldA
    ,   CASE 
            WHEN CHARINDEX('/', REVERSE(FieldA)) = 1 THEN REVERSE(SUBSTRING(REVERSE(FieldA),2,CHARINDEX('/', SUBSTRING(REVERSE(FieldA), 2, LEN(FieldA))) - 1)) 
            ELSE REVERSE(SUBSTRING(REVERSE(FieldA),1,CHARINDEX('/', REVERSE(FieldA)) - 1))
END
FROM (
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA 
  UNION ALL
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' 
) a

How do I get the index from the last occurence of a character in a string sql?

You can;

;with T(f) as (
    select '' union 
    select 'xxx' union 
    select 'xxx (yyy)' union 
    select 'xxx (yyy) (zzz)'
)
select 
    right(f, patindex('%(%', reverse(f) + '('))
from T

>>

(No column name)

xxx
(yyy)
(zzz)

Remove the +'(' to get blanks for no parens.

IndexOf function in T-SQL

CHARINDEX is what you are looking for

select CHARINDEX('@', 'someone@somewhere.com')
-----------
8

(1 row(s) affected)

-or-

select CHARINDEX('c', 'abcde')
-----------
3

(1 row(s) affected)

Getting rid of characters in a string

You can do :

select col, replace(col, right(col, charindex('/', reverse(col) + '/' ) -1), '')
from table t;

This assumes Folders always contains filenames.

Fastest way to find string by substring in SQL?

If you want to use less space than Randy's answer and there is considerable repetition in your data, you can create an N-Ary tree data structure where each edge is the next character and hang each string and trailing substring in your data on it.

You number the nodes in depth first order. Then you can create a table with up to 255 rows for each of your records, with the Id of your record, and the node id in your tree that matches the string or trailing substring. Then when you do a search, you find the node id that represents the string you are searching for (and all trailing substrings) and do a range search.

---

Related Topics