Indexof Function in T-Sql

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)

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

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]))))

Function-based indexes in SQL Server

I researched a bit further based on Damien's comment and found an answer that comes very close to matching Oracle's/PostgreSQL's function based indexes.

I have a table named PARCELS where I created a new column COMPUTEDPARCELS by using the alter statement as given below:

ALTER TABLE [PARCELS] ADD COMPUTEDPARCELS AS CONVERT(CHAR(8), [MAPNO], 112);

And then create an index on the computed column:

CREATE INDEX function_index ON [PARCELS](COMPUTEDPARCELS);

Of course the example is pretty simple but behaves just like a function based index.

T-SQL Index Of First Character In a String

I use PATINDEX:

select patindex('%[0-9]%', 'My1String')

Using LastIndexOf and SubString in mssql

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

SQL Using a String Function on Indexed Column Performance

The reason there is a difference is because the RIGHT(ID,10) has to be resolved for every row in order to filter it (hence the scan), whereas the ID = '00000000009123548754' clause can be resolved to a simple seek.

In technical terms, your first query is sargable, while the second isn't (due to the function).

Get index of first occurence of char in string from the end in t-sql

Reverse the string.

SET @Reversed = Reverse(@month_col);
WHILE @index<= @len
 BEGIN
 set @char = SUBSTRING(@Reversed, @index, 1)
 IF @char = 'W' 
 begin
    select @first_day = CHARINDEX(@char, @Reversed)
    break
 end
 SET @index= @index + 1
END
SET @lastday= @len - @first_day + 1   

EDIT: I think your original code can be much simpler. There's no need for the loop, use CHARINDEX and that's it.

SELECT @first_day = CHARINDEX('W', @month_col, 1)

My code would look like this:

SELECT @first_day = CHARINDEX('W', REVERSE(@month_col), 1)
SET @lastday= @len - @first_day + 1

In SQL Server can you find the first number in a string?

You can use the PATINDEX function instead of CHARINDEX, here lies the documentation ;)

---

Related Topics